0

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?

2

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 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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