Order of where clause evaluation

Question

If I have a MySQL table which has content from the last 3 months I actually do the following:

SELECT SQL_BUFFER_RESULT SQL_CACHE count(id) cnt
FROM `mDelivered` 
where _campaign = 2
  and `session` = '0082b570177d2cee48702a21c7b2484ca4c9f24f'
  and timeCreated > NOW() - INTERVAL 1 HOUR
group by `_campaign`, `session`

Now I think it first checks all elements where _campaign=2, is that right? Then it checks where session = ... and then it checks where _timeCreated > ....

How can I make the select faster? Is it better to reduce the size by changing the order of the element like this:

SELECT SQL_BUFFER_RESULT SQL_CACHE count(id) cnt
FROM `mDelivered`
where  timeCreated > NOW() - INTERVAL 1 HOUR
  and _campaign = 2
  and `session` = '0082b570177d2cee48702a21c7b2484ca4c9f24f'
group by `_campaign`, `session`

Or is it better to:

SELECT SQL_BUFFER_RESULT SQL_CACHE count(id) cnt
from (
  SELECT *
  FROM `mDelivered`
  where  timeCreated > NOW() - INTERVAL 1 HOUR
)
WHERE _campaign = 2
  and `session` = '0082b570177d2cee48702a21c7b2484ca4c9f24f'
group by `_campaign`, `session`

or how should this be done to be faster?

score 2 · Answer 1 · edited Apr 13 '17 at 12:42

See this answer. No, the order you put the conditions in the WHERE clause doesn't matter: In MySQL, does the order of the columns in a WHERE clause affect query performance?

If id is not nullable, you can replace the count(id) with count(*) and try adding an index on (_campaign, session, timeCreated).

ALTER TABLE mDelivered
  ADD INDEX campaign_session_timeCreated_IX        -- choose a name for the index
    (_campaign, session, timeCreated) ;

And you don't need the GROUP BY since you are already restricting the rows to fixed _campaign and session values. Try this:

SELECT count(*) cnt
FROM mDelivered
WHERE _campaign = 2
  AND `session` = '0082b570177d2cee48702a21c7b2484ca4c9f24f' 
  AND timeCreated > NOW() - INTERVAL 1 HOUR ;

Order of where clause evaluation

1 Answers1