0

I have a performance issue with a MySQL query that takes a long time. I'm trying to figure out why.

I'm using Django, a web framework that comes with ORM (Object relational Mappers) and basically generates my SQL statements. Of course I can also write custom SQL if needed.

The query that works but takes a long time is:

SELECT DISTINCT `trackdata_lead`.`id`,
            `trackdata_lead`.`company_name`,
            `trackdata_lead`.`status`,
            `trackdata_lead`.`company_uid`,
            `trackdata_lead`.`company_zipcode`,
            `trackdata_lead`.`company_city`,
            `trackdata_lead`.`company_canton`
FROM   `trackdata_lead`
   INNER JOIN `trackdata_trackrecord`
           ON ( `trackdata_lead`.`id` = `trackdata_trackrecord`.`lead_id` )
WHERE  ( `trackdata_lead`.`site_id` = 41
     AND `trackdata_trackrecord`.`timestamp` >= '2016-12-20 23:00:00'
     AND `trackdata_trackrecord`.`timestamp` <= '2016-12-25 22:59:59' );

This query takes about 19 seconds and returns 531 rows:

531 rows in set (19.29 sec)

I have ran EXPLAIN to check what MySQL is doing:

mysql> EXPLAIN SELECT DISTINCT `trackdata_lead`.`id`, `trackdata_lead`.`company_name`,
`trackdata_lead`.`status`, `trackdata_lead`.`company_uid`, `trackdata_lead`.`company_zipcode`,
`trackdata_lead`.`company_city`, `trackdata_lead`.`company_canton` FROM `trackdata_lead` INNER JOIN
`trackdata_trackrecord` ON (`trackdata_lead`.`id` = `trackdata_trackrecord`.`lead_id`) WHERE
(`trackdata_lead`.`site_id` = 41 AND `trackdata_trackrecord`.`timestamp` >= '2016-12-20 23:00:00' AND
`trackdata_trackrecord`.`timestamp` <= '2016-12-25 22:59:59') \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: trackdata_lead
         type: ref
possible_keys: PRIMARY,trackdata_lead_site_id_d3b7ac3d_fk_customer_site_id
          key: trackdata_lead_site_id_d3b7ac3d_fk_customer_site_id
      key_len: 4
          ref: const
         rows: 4269
        Extra: Using temporary
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: trackdata_trackrecord
         type: ref
possible_keys: trackdata_trackrecord_d7e6d55b,trackdata_trackrecord_8f635513
          key: trackdata_trackrecord_8f635513
      key_len: 5
          ref: permaleads.trackdata_lead.id
         rows: 64
        Extra: Using where; Distinct
2 rows in set (0.00 sec)

I think it takes a long time since it uses a temporary table. I have the following indexes:

mysql> SHOW INDEX FROM trackdata_trackrecord;
+-----------------------+------------+-------------------------------------------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table                 | Non_unique | Key_name                                              | Seq_in_index | Column_name        | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-----------------------+------------+-------------------------------------------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| trackdata_trackrecord |          0 | PRIMARY                                               |            1 | id                 | A         |     3708890 |     NULL | NULL   |      | BTREE      |         |               |
| trackdata_trackrecord |          1 | trackdata_trackrecord_d7e6d55b                        |            1 | timestamp          | A         |     3708890 |     NULL | NULL   |      | BTREE      |         |               |
| trackdata_trackrecord |          1 | trackdata_trackrecord_59fbc8df                        |            1 | ipaddress          | A         |      148355 |     NULL | NULL   |      | BTREE      |         |               |
| trackdata_trackrecord |          1 | trackdata_trackrecord_9365d6e7                        |            1 | site_id            | A         |          84 |     NULL | NULL   |      | BTREE      |         |               |
| trackdata_trackrecord |          1 | trackdata_trackrecord_4a0c0377                        |            1 | matched_inetnum_id | A         |       14835 |     NULL | NULL   | YES  | BTREE      |         |               |
| trackdata_trackrecord |          1 | trackdata_trackrecord_processed_2ab57daab18db765_uniq |            1 | processed          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| trackdata_trackrecord |          1 | trackdata_trackrecord_8f635513                        |            1 | lead_id            | A         |       57951 |     NULL | NULL   | YES  | BTREE      |         |               |
| trackdata_trackrecord |          1 | trackdata_trackrecord_considered_cce21f93_uniq        |            1 | considered         | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+-----------------------+------------+-------------------------------------------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
8 rows in set (0.00 sec)

mysql> SHOW INDEX FROM trackdata_lead;
+----------------+------------+------------------------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table          | Non_unique | Key_name                                                         | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------+------------+------------------------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| trackdata_lead |          0 | PRIMARY                                                          |            1 | id          | A         |       29727 |     NULL | NULL   |      | BTREE      |         |               |
| trackdata_lead |          1 | trackdata_le_company_id_b11050a8_fk_companydata_companyrecord_id |            1 | company_id  | A         |       14863 |     NULL | NULL   |      | BTREE      |         |               |
| trackdata_lead |          1 | trackdata_lead_site_id_d3b7ac3d_fk_customer_site_id              |            1 | site_id     | A         |          94 |     NULL | NULL   |      | BTREE      |         |               |
+----------------+------------+------------------------------------------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

Is there something I can optimize to gain performance? Any help is highly appreciated.

1
SELECT  `id`,
        `company_name`,
        `status`,
        `company_uid`,
        `company_zipcode`,
        `company_city`,
        `company_canton`
FROM   `trackdata_lead` AS tl
WHERE  `site_id` = 41
  AND EXISTS( SELECT * FROM `trackdata_trackrecord` AS tr
                WHERE tl.id = tr.lead_id
                  AND `timestamp` >= '2016-12-20 23:00:00'
                  AND `timestamp`  < '2016-12-20 23:00:00' + INTERVAL 6 DAY );

Indexes:

trackdata_lead:   INDEX(site_id)
trackdata_trackrecord:  INDEX(lead_id, timestamp) -- in that order

Notes:

  • JOIN + DISTINCT (or GROUP BY) leads to an explosion or rows, followed by an implosion. By moving the JOIN to EXISTS, both can be eliminated (I think).
  • EXISTS stops when it finds any record, and does not need to look at all matches.
  • The subquery is 'covering' ("Using index"), hence very efficient
  • The rewriting of the timestamp condition is not for speed, but for avoiding some end cases and to avoid computing around leap days, etc.
  • A 'composite' index is not the same as two single-column indexes.
  • INDEX(lead_id) will now be redundant and should be DROPped.
  • There seem to be two 'flags' being indexed; generally such indexes are never used.
  • The EXPLAIN suggests that about 4269*64 rows will be looked at (and put into a tmp table). My formulation should touch only about 4269*2 rows -- scan 4269 rows in one table, plus make 4269 probes into the other. So, I would expect a significant speedup.
  • Hey Rick - Thanks for your explanation. I ran it but it takes even more time (about 34 seconds). Do I have to create new indexes? Thanks for the pointer to the timestamp optimization. – Daniel Jan 12 '17 at 4:43
  • The EXPLAIN for your query: pastebin.com/BFrz4pUV – Daniel Jan 12 '17 at 5:21
  • Yes the new indexes are part of the solution. – Rick James Jan 12 '17 at 5:36
  • You're awesome! It works! – Daniel Jan 12 '17 at 10:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.