MySQL query optimisation - long running query

Question

I have a query in mysql that is taking a long time to execute. There are about 350k records in trans table, and about 1m records in trans_detail table

I'd appreciate some help in optimising the query or database structure.

The query:

SELECT
    DATE_FORMAT(t.utc_date_due, '%b %Y') AS date_trans,
    LAST_DAY(t.utc_date_due) AS date_end,
    SUM(td.amount * lode) AS 'amount',
    SUM(t.amount_paid * lode) AS 'paid'
FROM trans t
LEFT JOIN trans_detail td ON t.id = td.trans_id AND td.ident = 'c'
WHERE t.company_id = 1
    AND  (trans_type_id = 'inv' or trans_type_id = 'crn')
    AND t.is_deleted = 0
    AND t.is_draft = 0
GROUP BY DATE_FORMAT(t.utc_date_due, '%b %Y')
ORDER BY utc_date_due

The explain:

+----+-------------+-------+-------------+----------------------------------------------------------+--------------------------------------+---------+----------------+------+-----------------------------------------------------------------------------------------------------+
| id | select_type | table | type        | possible_keys                                            | key                                  | key_len | ref            | rows | Extra                                                                                               |
+----+-------------+-------+-------------+----------------------------------------------------------+--------------------------------------+---------+----------------+------+-----------------------------------------------------------------------------------------------------+
|  1 | SIMPLE      | t     | index_merge | fk_trans_company,fk_trans_trans_type,is_deleted,is_draft | fk_trans_company,is_draft,is_deleted | 4,1,2   | NULL           |  995 | Using intersect(fk_trans_company,is_draft,is_deleted); Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | td    | ref         | fk_trans_detail_trans,ident                              | fk_trans_detail_trans                | 4       | actester2.t.id |    1 |                                                                                                     |
+----+-------------+-------+-------------+----------------------------------------------------------+--------------------------------------+---------+----------------+------+-----------------------------------------------------------------------------------------------------+

score 1 · Answer 1 · answered Jul 26 '12 at 15:30

1

I'd start by changing the line:

AND  (trans_type_id = 'inv' or trans_type_id = 'crn')

to use the IN clause:

AND  (trans_type_id in ('inv', 'crn'))

answered Jul 26 '12 at 15:30

Widor

13,003
7
42
64

The two are equivalent, there's no performance to be gained from using either one of them. – Lieven Keersmaekers Jul 26 '12 at 15:33
Often, but not always - it depends whether the field is indexed. http://stackoverflow.com/questions/3074713/in-vs-or-in-the-sql-where-clause – Widor Jul 26 '12 at 15:36
+1. thx for the link. Good to know but it doesn't matter for indexed columns. – Lieven Keersmaekers Jul 26 '12 at 15:41

score 0 · Answer 2 · answered May 18 '13 at 15:55

Try to put

td.ident = 'c'

in your WHERE clause. You may create an index on utc_date_due.

Personally I would suggest, if speed is crucial, to use a two step approach, you should use a temporary table and insert your elements, and then make sums only on the temporary table.

MySQL query optimisation - long running query

2 Answers2