how to optimize mysql query if i have too many OR

问题:

using PHP and MySQL

i have grabbed an array of facebook user ids from facebook.

Now i want to find the corresponding username in my application for this array.

Clearly in my application the user table contains unique username and unique fb_uid values.

my rudimentary understanding oof programming led me to 2 ways:

1) use a loop and run through the array of fb_uid and find the username one by one.

OR

2) create a monster query like select distinct(username) from users where fb_uid = value1 OR fb_uid = value2 ...

so is there a better way out?

Thank you.

回答1:

Use SQL's IN operator instead:

select distinct(username) from users where fb_uid in (value1, value2, ...)

http://www.w3schools.com/SQl/sql_in.asp

回答2:

If your list of fb_uids is big (say, more than 100 or 500 r 1000 hundred ids), I wouldn't go with the "or" way : too many "or" like this will hurt the DB, I think.

But doing one query per id is not really good either...

So what about a mix of those two ideas ? Doing one query per, say, 50 or 100 fb_uids ?

And, instead of using lots or OR, you can go with IN ; a bit like this :

select distinct(username) from users where fb_uid IN (id1, id2, id3, ...)

Not sure it'll change anything for the DB, but, at least, it's looking better ^^

Only thing is you shouldn't use too many ids in the IN ; so, doing a couple of queries, with between 50 and 500 ids each time might be the way to go.

And to help you a bit further, you might need to take a look at array_slice, to extracts "slices" of your fb_uids array...

回答3:

There's another MySQL function: MATCH AGAINST

SELECT * FROM customer WHERE status = 1 AND MATCH (person, city, company, zipcode, tags) AGAINST ('".$searchstring."' IN BOOLEAN MODE)

Your inputstring will be searched in different columns.

Maybe you can reconsider your sql statement if you didn't knew this function.