为获得快速结果，我的查询有什么好的索引？

I have a query that is pulling form many tables but it is slow and I want to improve it.

我有一个查询,从许多表拉,但它很慢,我想改进它。

SELECT 
    ph.phone_call_id AS id,
    ph.call_subject AS callSubject,
    ac.account_name AS accountName,
    ph.trigger_on AS triggerOn,
    ph.isAppointment,
    ph.status,
    ind.name AS industry,
    cc.call_code_name AS callCode,
    ac.account_id 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-10-09 00:00:00' AND '2013-04-09 23:59:59' 
LIMIT 0,25

Note that phone_Calls table has around 4.5 million records and accounts around 300k records.

请注意,phone_Calls表有大约450万条记录和约300k条记录。

I have index like this now

我现在有这样的索引

ph.trigger_on
ph.owner_id
ph.status


ac.status
ac.do_not_call
ac.account_id

here is what I get when I explain the query

这是我解释查询时得到的内容

2 个解决方案

#1

It is important to remember that a column that makes a quality index is one that has a high degree of uniqueness. I'm not sure how many status values there are in your system, but it is not a good index if there are say only 3 possible statuses for your table.

重要的是要记住,制作质量指数的列是具有高度唯一性的列。我不确定您的系统中有多少状态值,但如果表中只有3种可能的状态,则它不是一个好的索引。

It would also be helpful to have your indexes be on columns that are central to the join operation you are preforming. In this case ph.account_id , ac.account_id, ind.industry_id, ac.industry_id, ph.call_code_id, ph.owner_id and cc.call_code_id all look like good index candidates (if they are not already indexes). Note that if any of your tables has a lot of indexes (over five-ish) this has the potential to slow your queries down. Additionally, if any of your tables involved have a large number of writes or deletes preformed on them (as in comparable to the number of reads) then indexing these tables heavily is unwise because each index file is rewritten much more frequently on tables that have many write operations preformed on them. It has been my experience that indexing is an art form more than science so you'll have to do some experimentation to find what is best for your system. Experiment offline if possible to see what gives the best boost given your dataset. Good Luck.

将索引放在对预先形成的连接操作至关重要的列上也会很有帮助。在这种情况下,ph.account_id,ac.account_id,ind.industry_id,ac.industry_id,ph.call_code_id,ph.owner_id和cc.call_code_id都看起来像是好的索引候选者(如果它们不是索引)。请注意,如果您的任何表具有大量索引(超过五个),则可能会降低查询速度。此外,如果涉及的任何表在其上执行了大量写入或删除(与读取次数相当),那么对这些表进行大量索引是不明智的,因为每个索引文件在具有许多表的表上更频繁地重写在它们上面执行写操作。根据我的经验,索引是一种艺术形式而不是科学,因此您必须进行一些实验才能找到最适合您系统的方法。如果可能,离线实验,看看在给定数据集的情况下最佳效果。祝你好运。

2 个解决方案

#1

更多相关文章

随机推荐