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?
Best Answer
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 thecount(id)
withcount(*)
and try adding an index on(_campaign, session, timeCreated)
.And you don't need the
GROUP BY
since you are already restricting the rows to fixed_campaign
andsession
values. Try this: