ORDER BY datetime使查询非常慢 [英] ORDER BY datetime makes the query very slow

问题描述

我正在尝试从多个表中提取数据，当我在日期时间字段中使用ORDER BY时，它会在至少10秒后返回结果，但是如果我在没有ORDER BY的情况下执行相同的查询，则它会在2秒内返回结果.

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.

这是我当前的查询

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类型

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

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

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

ac.status 
ph.status
ac.do_not_call

此字段是日期时间类型

ph.trigger_on

请注意，该帐户拥有30万条记录，而phone_calls具有500万条记录. 我怎样做才能更快地执行此ORDER BY?请注意，我的所有where子句字段，我的所有ON子句和ph.trigger_on都已建立索引.而且我使用的是InnoDB存储引擎而不是MyIsam.

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.

谢谢

推荐答案

请尝试以下操作:

1. 在列(phone_calls.trigger_on, phone_calls.status, phone_calls.owner_id)上建立索引pcto

将FROM子句更改为:

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.

请注意:在查询中的每"列上建立索引并不重要(实际上也没有好处). MySQL只能在每个表中使用一个索引(或更正确地在每个表别名中使用).您需要建立我们要告诉您的索引.

这篇关于ORDER BY datetime使查询非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！