Let's say I have a private message table like this:
I want to fetch all PM sent or received for a user. uid1 will be sender, uid2 receiver. Can I put up an INDEX for this? I've been reading some and it seems OR can't use indexes, or am I wrong? I tried setting up a composite index called uid1ui2 for (uid1,uid2) instead of indexes for uid1 and uid2. Doesn't really seem to show any difference in EXPLAIN. using AND seems to work for composite (which might be what it's supposed to do).Code:SELECT id , uid1 , uid2 , msg , msgdate FROM user_pm WHERE uid1 = 999 OR uid2 = 999 ORDER msgdate DESC LIMIT 10
I have a similar setup which will need to be searched through on every page refresh (not PM) so for that query I'd want to use indexes.
Should I just do two separate queries, WHERE uid1=999 and another one with uid2=999 (with an index on each column), and then do some sorting in php (will probably need to set a higher LIMIT to actually get *close to* everything), or is there a better way?