我正在尝试从多个表中提取数据,当我在日期时间字段中使用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.
谢谢
推荐答案请尝试以下操作:
在列(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使查询非常慢
发布评论