CODEWITHSUNDEEP
mysqlsql
deltanovember
deltanovember

Reputation: 44061

Why is my SQL so slow?

My table is reasonably small around 50,000 rows. My schema is as follows:

DAILY

Query:

SELECT user_id 
  FROM `daily` 
 WHERE user_id IN (SELECT user_id 
                     FROM daily 
                    WHERE round > 25 
                      AND tournament_id = 24 
                      AND (result = 'Won' OR result = 'Lost'))

Upvotes: 0

Views: 161

Answers (4)

Chandu
Chandu

Reputation: 82933

First verify and add Indexes as suggested earlier.

Also why are you using an in if you are querying data from same table. Change your query to:

SELECT user_id 
FROM   daily 
WHERE  round > 25 
       AND tournament_id = 24 
       AND ( result = 'Won' 
              OR result = 'Lost' )

Upvotes: 2

OMG Ponies
OMG Ponies

Reputation: 332661

Your query only needs to be:

SELECT d.user_id 
  FROM DAILY d
 WHERE d.round > 25 
   AND d.tournament_id = 24 
   AND d.result IN ('Won', 'Lost')

Indexes should be considered on:

  • DAILY.round
  • DAILY.tournament_id
  • DAILY.result

Upvotes: 1

Ryk
Ryk

Reputation: 3102

This should return in a millisecond.

SELECT user_id FROM daily WITH(NOLOCK) 
where user_id in (select user_id from daily WITH(NOLOCK)  where round > 25 and tournament_id = 24 and (result = 'Won' or result = 'Lost'))

Then make sure there is an index on the filter columns.

CREATE NONCLUSTERED INDEX IX_1 ON daily (round ASC, tournament_id ASC, result ASC)

Upvotes: 0

iwalkbarefoot
iwalkbarefoot

Reputation: 955

Using the in keyword in the fashion you are is a very dangerous [from a performance perspective] thing to do. It will result in the sub query [(select user_id from daily where round > 25 and tournament_id=24 and (result='Won' or result='Lost'))] being ran 50,000 times in this case.

You'll want to convert this onto a join something to the effect of

select user_id from daily a join
       (select user_id from daily where round > 25 and tournament_id=24 and (result='Won' or result='Lost')) b on a.user_id = b.user_id

Doing something similar to this will result in only two queries and a join.

As Cybernate pointed out in your specific example you can simply use where clauses, but I went ahead and suggested this in case your query is actually more complex than what you posted.

Upvotes: 3

Related Questions