Making my SQL Query more efficient

Question

I've got millions of entries in my DB.

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?

This is my query:

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`

Unless you provide more information, then no. Feel free to take a look at what info people provide when asking for MySQL query performance improvements. — N.B.
– N.B., Commented Jan 30, 2013 at 8:52

Kangkan · Accepted Answer · 2013-01-30 08:54:29Z

2

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.

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

answered Jan 30, 2013 at 8:54

Kangkan

15.6k11 gold badges79 silver badges116 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kangkan Over a year ago

@DanRevah: What change gave you the most significant improvement?

CloudyMarble · Accepted Answer · 2013-01-30 09:06:09Z

2

You can try to optimize your query by analyzing the Explain plan, including index optimizing which can be very helpful if you still dont have idnexes on your tables. regarding the statement optimizing take a look here.

Hope this helps you further

edited Jan 30, 2013 at 9:06

answered Jan 30, 2013 at 8:52

CloudyMarble

37.7k70 gold badges103 silver badges133 bronze badges

2 Comments

CloudyMarble Over a year ago

@ypercube your right its actually only a syntax issue, just learned something, thank you.

ypercubeᵀᴹ Over a year ago

I prefer c >= '2013-01-28' AND c < '2013-01-29 because it's easier to code, works with any datetime type (date, datetime, timestamp) and does not break if the column's datatype has higher than second granularity.

Collectives™ on Stack Overflow

Making my SQL Query more efficient

2 Answers 2

1 Comment

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related