Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I found this query in our web app that has been made by hired programmers. It performs like a dog and needs optimization, but I'm no MySQL wizard (yet), unfortunately. Please help, anything is appreciated.

This is the query:

SELECT DISTINCT test_user_info_test.ID
FROM test_user_info_test, test_user_saver_test, test_user_filter_test
WHERE test_user_info_test.ACTIVE = '1'
   AND (
         ( test_user_info_test.BALANCE >= '1' AND test_user_info_test.ID IN
           (
              SELECT USERID
              FROM test_user_filter_test
              WHERE symbol = 'x'
                     AND INCLUDE ='1')
           ) 
        OR 
           (
        HAS_IT = '1' AND EXPIRE > '1337500000'
        AND
        test_user_info_test.ID IN
              (
                 SELECT USERID
                 FROM test_user_saver_test
                 WHERE symbol = 'x'
                      AND INCLUDE = '1'
              )
          )
     )

Currently, the tables don't have any indexes.

Description of what the query is supposed to do: it needs to select the user id's from active users that have a positive balance and have set a filter for symbol X, in this example. Also it needs to select the user id's from users who have a special subscription that hasn't expired yet and have set a filter for symbol X.

I think the execution of the query can take up to quite a few minutes, which is clearly unacceptable.

share|improve this question
    
I would first change the FROM test_user_info_test, test_user_saver_test, test_user_filter_test to the simple FROM test_user_info_test. And also remove the DISTINCT. And then of course test if the resulting query gives the same results as this one! –  ypercube May 22 '12 at 20:13
    
I think the rest is redundant (the subqueries take care of testing those tables). –  ypercube May 22 '12 at 20:14
    
How are the tables indexed? Can you post an execution plan? –  FrustratedWithFormsDesigner May 22 '12 at 20:17
1  
Can you just delete the query? Joking aside, some background on what you're expecting the query to do would be helpful (an english sentence describing the question and answer). –  Derek Downey May 22 '12 at 21:47
    
It will be helpfull to see the ERD of the schema. –  Ohadi May 23 '12 at 11:21
show 2 more comments

3 Answers

Here's a modification you could make... (caveat - I'm a SQL Server person):

SELECT DISTINCT 
  test_user_info_test.ID
FROM 
  test_user_info_test, 
  left outer join test_user_saver_test
    on test_user_info_test.id = test_user_filter_test.USERID
    and symbol = 'x'
    AND INCLUDE ='1'
  left outer test_user_filter_test
    on test_user_info_test.id = test_user_saver_test.USERID
    and symbol = 'x'
    AND INCLUDE = '1'
WHERE 
  test_user_info_test.ACTIVE = '1'
  AND 
  (
  (test_user_info_test.BALANCE >= '1' and test_user_filter_test.USERID is not null)
  or
  (test_user_info_test.HAS_IT = '1' AND test_user_info_test.EXPIRE > '1337500000' and test_user_saver_test.USERID is not null)
  )

Also add indexes on test_user_info_test.id, test_user_info_test.id, test_user_saver_test.USERID.

Those are the quick things that jump out at me...

share|improve this answer
1  
I'm thinking the use of implicit syntax is one of the reasons this is slow, it appears to me that the OP is doing accidental cross joins. This is one of the reasons why people in the 21st century should not still be writing code with implicit joins. It is a SQL antipattern and was replaced with a better syntax in 1992. And you may not need the distinct anymore with the accidental crossjoins removed. –  HLGEM May 23 '12 at 21:23
    
I think you havea an unneded comma in the code sample left over from the implicit joins. –  HLGEM May 23 '12 at 21:25
add comment

Thanks everyone! I did some testing and I ended up with combining 2 solutions:

  1. I would first change the FROM test_user_info_test, test_user_saver_test, test_user_filter_test to the simple FROM test_user_info_test (thanks to ypercube)

  2. Also add indexes on test_user_info_test.id, test_user_info_test.id, test_user_saver_test.USERID (thanks to JHFB)

After implementing these changes, the query was flying!

JHFB's suggested query didn't work on (my version of?) MySQL, so maybe it contains some SQL Server specific stuff. Since the original query is now performing well, I didn't bother trying to make JHFB's query work.

Thank you all very much for helping me out here, much appreciated!

share|improve this answer
add comment

JHFB turned "IN (SELECT...)" into JOINs -- this is almost always a performance improvement.

OR is another issue -- You may need to turn that into the UNION of two SELECTs.

Please use short aliases (like "FROM test_user_info_test AS i"); I find your SELECT so cluttered with long, similar, table names that I cannot read it.

Please provide SHOW CREATE TABLE and EXPLAIN if you desire further discussion.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.