ORDER BY datetime makes the query very slow

Question

I am trying to pull data from multiple tables and when I user ORDER BY a datetime field it return the results after at least 10 seconds but if I do the same query without ORDER BY then it return the results for under 2 seconds.

This is my current query

SELECT
ph.call_subject AS callSubject,
ac.account_name AS accountName,
DATE_FORMAT(ph.trigger_on, "%c/%e/%Y %h:%i %p") AS triggerOn,
ind.name AS industry,
cc.call_code_name AS callCode
FROM phone_calls AS ph
INNER JOIN accounts AS ac ON ph.account_id = ac.account_id
INNER JOIN industries AS ind ON ind.industry_id = ac.industry_id
INNER JOIN call_codes AS cc ON ph.call_code_id = cc.call_code_id
WHERE ac.status = 1 AND ph.status = 1 AND ph.owner_id = 1 AND ac.do_not_call = 0
AND ph.trigger_on BETWEEN '2012-11-19 00:00:00' AND '2013-03-19 23:59:59'
ORDER BY ph.trigger_on ASC LIMIT 0,1000

the following fields are all of the type INT(11) UNSIGNED

ph.account_id
ac.account_id
ind.industry_id
ac.industry_id
ph.call_code_id
cc.call_code_id
ph.owner_id

The following fields are all of the type tinyint(1)

ac.status 
ph.status
ac.do_not_call

this field is a datetime type

ph.trigger_on

Please note that has accounts 300K records and phone_calls has 5 million records. What can I do to preform this ORDER BY faster? note that all of my where clause fields, all my ON clause and ph.trigger_on are indexed. and I am using InnoDB storage engine not MyIsam.

Thanks

Answer 1

Please try this:

1. Build an index on the columns (phone_calls.trigger_on, phone_calls.status, phone_calls.owner_id) Call it pcto

2. Change your FROM clause to:

    FROM phone_calls AS ph FORCE INDEX (pcto)

This is the ideal. If it does not work, then add a comment and I will give you another method that it guaranteed to work and give you the performance improvement that you need.

PLEASE NOTE: It doesn't matter (and indeed does no good) to have indexes built on "every" column in your query. MySQL can only use ONE index per table (or more correctly per table alias). You need to build the indexes that we are telling you to.