mysql – ORDER BY datetime使查询非常慢

这是我目前的查询

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

以下字段都是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

以下字段均为tinyint类型(1)

ac.status 
ph.status
ac.do_not_call

此字段是日期时间类型

ph.trigger_on

请注意,帐户有300K记录,phone_calls有500万条记录.
我该怎么做才能更快地执行此ORDER BY？请注意,我的所有where子句字段,所有我的ON子句和ph.trigger_on都被编入索引.我正在使用InnoDB存储引擎而不是MyIsam.

谢谢

>在列上构建索引(phone_calls.trigger_on.phone_Calls.status,phone_calls.owner_id)将其命名为pcto
>将FROM子句更改为：

来自phone_calls AS ph FORCE INDEX pcto

这是理想的.如果它不起作用,那么添加一个注释,我将为您提供另一种方法,它可以保证工作并为您提供所需的性能改进.

请注意：在查询中的“每个”列上构建索引并不重要(实际上没有用). MySQL每个表只能使用一个索引(或者每个表别名更正确).您需要构建我们告诉您的索引.