Dog slow query, needs optimizing, please help

Question

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.

Answer 1

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...

Answer 2

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!

Answer 3

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.