使我的SQL查询更高效 [英] Making my SQL Query more efficient

问题描述

我的数据库中有数百万个条目.

I've got millions of entries in my DB.

，因为即时通讯使用了3个！内部联接并放置大范围的日期，此查询实际上要花费很长时间，最多可能需要几分钟.

and because im using 3!! inner joins and put large range of dates this query takes really long like up to few minutes.

有没有一种方法可以改善此查询并仍然获得相同的数据?

Is there a way to Improve this query and still get the same Data?

这是我的查询:

SELECT 
    subscriptions.service_id, 
    service_send_type.usage, 
    service_send_type.price, SUM(IF(msg_t.status LIKE 'Success%', 1, 0)) AS s, 
    COUNT(1) AS t 
FROM (`subscriptions`) 
    INNER JOIN 
        `msg_t` ON `subscriptions`.`phone` = `msg_t`.`phone` AND subscriptions.id = msg_t.sub_id 
    INNER JOIN
        `msg` ON `msg`.`id` = `msg_t`.`msg_id` 
    INNER JOIN `service_send_type` ON `msg`.`service_id` = `service_send_type`.`service_id` 
        AND msg.sushi_service_id = service_send_type.sushi_service_id 
        AND msg.send_type = service_send_type.name 
        AND msg.service_id = subscriptions.service_id 

    WHERE 
        `subscriptions`.`service_id` IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16) 
        AND 
        `subscriptions`.`added` >= '2013-01-28 00:00:00' 
        AND 
        `subscriptions`.`added` <= '2013-01-28 23:59:59' 
        AND 
        `msg_t`.`send_time` >= '2013-01-28 00:00:00' 
        AND 
        `msg_t`.`send_time` <= '2013-01-28 23:59:59' 

    GROUP BY 
        `subscriptions`.`service_id`, 
        `service_send_type`.`usage`, 
        `service_send_type`.`price`

推荐答案

是否始终需要IN查询?在您的查询中，IN子句可以用subscriptions.service_id < 17替换，这将带来很大的不同.当您有一个离散值列表时，IN查询是合适的.

Do you always need to have the IN query? In your query, the IN clause can be replaced by subscriptions.service_id < 17 and that will make a huge difference. IN queries are suitable when you have a list of discrete values.

还要查看表上的索引.您应该在where条件和group by子句中的列上设置索引.

Also look at the indexing on the tables. You should have index set on columns in the where condition and group by clauses.

这篇关于使我的SQL查询更高效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！