ORDER BY datetime makes the query very slow
mysql, sql-order-by
Solution
Please try this:
Build an index on the columns `(phone_calls.trigger_on, phone_calls.status, phone_calls.owner_id)` Call it `pcto`
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.
Problem
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