Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 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 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 ;
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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