Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Mar 2009
    Posts
    175
    Thanks
    3
    Thanked 1 Time in 1 Post

    More efficient way to rewrite this query?

    I have a long query that is zapping resources and need to rewrite it. The obvious problem with it is the use of "not in" in the where clause. My initial thought is to rewrite it doing away with all of the self joins and doing a "not exists" on a subquery. Any thoughts on doing that, or a maybe a more efficient idea than that one?

    Here's the query:
    Code:
    SELECT a.referenceid, a.memberid AS d1, b.memberid AS d2, c.memberid AS d3, d.memberid AS d4, e.memberid AS d5, f.memberid AS d6 FROM jos_comprofiler_members AS a FORCE INDEX (aprm) LEFT JOIN jos_comprofiler_members AS b FORCE INDEX (aprm) ON a.memberid=b.referenceid AND b.accepted=1 AND b.pending=0 LEFT JOIN jos_comprofiler_members AS c FORCE INDEX (aprm) ON b.memberid=c.referenceid AND c.accepted=1 AND c.pending=0 LEFT JOIN jos_comprofiler_members AS d FORCE INDEX (pamr) ON c.memberid=d.referenceid AND d.accepted=1 AND d.pending=0 LEFT JOIN jos_comprofiler_members AS e FORCE INDEX (pamr) ON d.memberid=e.referenceid AND e.accepted=1 AND e.pending=0 LEFT JOIN jos_comprofiler_members AS f FORCE INDEX (pamr) ON e.memberid=f.referenceid AND f.accepted=1 AND f.pending=0 WHERE a.referenceid = 1593 AND a.accepted=1 AND a.pending=0 AND f.memberid = 1593 AND b.memberid NOT IN ( 1593,a.memberid) AND c.memberid NOT IN ( 1593,a.memberid,b.memberid) AND d.memberid NOT IN ( 1593,a.memberid,b.memberid,c.memberid) AND e.memberid NOT IN ( 1593,a.memberid,b.memberid,c.memberid,d.memberid) AND f.memberid NOT IN ( 1593,a.memberid,b.memberid,c.memberid,d.memberid,e.memberid) LIMIT 0, 1

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,869
    Thanks
    79
    Thanked 4,418 Times in 4,383 Posts
    Let's start my making the query readable:
    Code:
    SELECT a.referenceid, a.memberid AS d1, b.memberid AS d2, c.memberid AS d3, d.memberid AS d4, e.memberid AS d5, f.memberid AS d6 
    FROM jos_comprofiler_members AS a FORCE INDEX (aprm) 
    LEFT JOIN jos_comprofiler_members AS b FORCE INDEX (aprm) ON a.memberid=b.referenceid AND b.accepted=1 AND b.pending=0 
    LEFT JOIN jos_comprofiler_members AS c FORCE INDEX (aprm) ON b.memberid=c.referenceid AND c.accepted=1 AND c.pending=0 
    LEFT JOIN jos_comprofiler_members AS d FORCE INDEX (pamr) ON c.memberid=d.referenceid AND d.accepted=1 AND d.pending=0 
    LEFT JOIN jos_comprofiler_members AS e FORCE INDEX (pamr) ON d.memberid=e.referenceid AND e.accepted=1 AND e.pending=0 
    LEFT JOIN jos_comprofiler_members AS f FORCE INDEX (pamr) ON e.memberid=f.referenceid AND f.accepted=1 AND f.pending=0 
    WHERE a.referenceid = 1593 AND a.accepted=1 AND a.pending=0 AND f.memberid = 1593 
    AND b.memberid NOT IN ( 1593,a.memberid) 
    AND c.memberid NOT IN ( 1593,a.memberid,b.memberid) 
    AND d.memberid NOT IN ( 1593,a.memberid,b.memberid,c.memberid) 
    AND e.memberid NOT IN ( 1593,a.memberid,b.memberid,c.memberid,d.memberid) 
    AND f.memberid NOT IN ( 1593,a.memberid,b.memberid,c.memberid,d.memberid,e.memberid) 
    LIMIT 0, 1
    Okay... for starters, you may *THINK* you have a series of LEFT JOINs there, but in fact because of your WHERE clause you have converted EVERY ONE OF THEM into an INNER JOIN!

    Look here:
    http://www.codingforums.com/showthre...192#post818192

    You will have to decide if you NEED those to be LEFT JOINs, so that you will then rewrite all the ON conditions, or if INNER JOINs are adequate.

    And then you need to use MySQL's EXPLAIN to see what it says it is going to do.

    You also have at least one self-contradictory condition in there. To wit:
    Code:
    WHERE ... AND f.memberid = 1593 ...
    ...
    AND f.memberid NOT IN ( 1593, ... )
    You have, effectively, said "never select a record from table f".

    Finally, I don't see how we can guess how to help you when you don't show the definitions of your two forced indexes.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #3
    Regular Coder
    Join Date
    Mar 2009
    Posts
    175
    Thanks
    3
    Thanked 1 Time in 1 Post
    Thanks for the reply, I never thought of those as inner joins.
    I did notice the contradiction on the f alias however which I'm not sure what the idea was behind that.

    I was able to track down exactly where that query was being created, which was in a function to determine the degree of separation between two members. We don't need or utilize that functionality so for the time being I simply commented the query out of the function, out of sight out of mind for now. At least this way if for some reason that functionality is needed down the road I can simply rewrite the query then.

    Just from curiosity, I realize joins usually perform better than subqueries, but in this case would subqueries make sense over how this was written?

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,869
    Thanks
    79
    Thanked 4,418 Times in 4,383 Posts
    Don't know the answer to your question, offhand.

    I would have to make some experiments. And I strongly suspect that looking carefully at what indexes are defined would be worth the time if you pursue this.

    Again, EXPLAIN is your first tool on something like this.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #5
    Regular Coder
    Join Date
    Mar 2009
    Posts
    175
    Thanks
    3
    Thanked 1 Time in 1 Post
    Thanks again!

  • #6
    Senior Coder timgolding's Avatar
    Join Date
    Aug 2006
    Location
    Southampton
    Posts
    1,519
    Thanks
    114
    Thanked 110 Times in 109 Posts

    Big sql statement very slow

    sorry didn't mean to post here
    You can not say you know how to do something, until you can teach it to someone else.


  •  

    Tags for this Thread

    Posting Permissions

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