SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Member
    Join Date
    Jun 2007
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Index when using OR?

    Let's say I have a private message table like this:

    Code:
    SELECT id
    , uid1
    , uid2
    , msg
    , msgdate
    
    FROM user_pm
    
    WHERE uid1 = 999 OR uid2 = 999
    
    ORDER msgdate DESC
    
    LIMIT 10
    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).

    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?

    Thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    put separate indexes on uid1 and uid2, then try this --
    Code:
    ( SELECT id
         , uid1
         , uid2
         , msg
         , msgdate
      FROM user_pm
     WHERE uid1 = 999 )
    UNION ALL
    ( SELECT id
         , uid1
         , uid2
         , msg
         , msgdate
      FROM user_pm
     WHERE uid2 = 999 )
    ORDER 
        BY msgdate DESC LIMIT 10
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jun 2007
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi r937

    I'm sorry, I think it somehow worked from the beginning. It was just that one user had 70 000 or so PM which made it all take a few extra seconds on the initial load. When testing EXPLAIN now I get

    Code:
    1  	SIMPLE  	user_pm  	index_merge  	uid1,uid2  	uid1,uid2  	3,3  	NULL  	2  	Using union(uid1,uid2); Using where; Using files...
    which seems to be right. There's no need to use the code you provided then right? It works, but *looks* like it would be slower, but... I wouln't really know

    I'll just have clean the table of old PM regularly I guess!

    Thanks

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    index merge? whoa!

    you are in the unique position of being able to test both queries and report back on the differences

    if performance is approximately equal, i prefer your query over mine because it's simpler, easier to understand

    also, please try it with
    Code:
    WHERE 999 IN ( uid1,uid2 )
    and see if that makes a difference

    thanks



    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Jun 2007
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cool, I guess!

    (I have changed back from composite index to separate btw.)

    Anyway, WHERE 999 (uid1,uid2) worked but was slow. Couldn't use index it seems.

    Code:
    	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
    1 	SIMPLE 	user_pm 	ALL 	NULL 	NULL 	NULL 	NULL 	238024 	Using where; Using filesort
    This is what EXPLAIN gives on the user with many PM's, using your query from before.

    Code:
    id 	select_type 	table 	type 	possible_keys 	key 	key_len 	ref 	rows 	Extra
    1 	PRIMARY 	user_pm 	ref 	uid1 	uid2 	3 	const 	854 	 
    2 	UNION 	user_pm 	ref 	uid1 	uid2 	3 	const 	68504 	 
    NULL 	UNION RESULT 	<union1,2> 	ALL 	NULL 	NULL 	NULL 	NULL 	NULL 	Using filesort
    Thanks


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •