Some context: migrating some data from a 3rd party cloud MongoDB platform service into MySQL.

Wrote a query but it's very slow. Here's the query:

SELECT Table1.*, (long list of fields for each Table2, Table3, Table4) FROM Table1 JOIN Table2 on creator = Table2.objectId JOIN Tabl3 on Table1.location = Table3.objectId JOIN Table4 on Table1.campaign = Table4.objectId WHERE Table1.campaign = 'some_object_id'

Here's the explain:

+----+-------------+------------------+--------+---------------------------------------+-------------+---------+----------------------------------------+------+-------------+
| id | select_type | table            | type   | possible_keys                         | key         | key_len | ref                                    | rows | Extra       |
+----+-------------+------------------+--------+---------------------------------------+-------------+---------+----------------------------------------+------+-------------+
|  1 | SIMPLE      | Table4         | const  | PRIMARY                               | PRIMARY     | 12      | const                                  |    1 | NULL        |
|  1 | SIMPLE      | Table2            | ALL    | PRIMARY                               | NULL        | NULL    | NULL                                   |  270 | NULL        |
|  1 | SIMPLE      | Table1 | ref    | location_idx,campaign_idx,creator_idx | creator_idx | 13      | parseObjects.Table2.objectId            |    1 | Using where |
|  1 | SIMPLE      | Table3 | eq_ref | PRIMARY                               | PRIMARY     | 12      | parseObjects.Table1.location |    1 | NULL        |
+----+-------------+------------------+--------+---------------------------------------+-------------+---------+----------------------------------------+------+-------------+
4 rows in set (0.29 sec)

Table2, Table3, and Table4 all have objectId as their primary key.

Here's the profile

+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000287 |
| checking permissions | 0.000011 |
| checking permissions | 0.000008 |
| checking permissions | 0.000007 |
| checking permissions | 0.000010 |
| Opening tables       | 0.000036 |
| init                 | 0.000113 |
| System lock          | 0.000016 |
| optimizing           | 0.000023 |
| statistics           | 0.000123 |
| preparing            | 0.000030 |
| executing            | 0.000009 |
| Sending data         | 5.481633 |
| end                  | 0.000023 |
| query end            | 0.000014 |
| closing tables       | 0.000027 |
| freeing items        | 0.000027 |
| cleaning up          | 0.000013 |
+----------------------+----------+
18 rows in set, 1 warning (0.13 sec)

This query is taking over 5.0 seconds on average, and I'm using a very small data size for testing: no table has more than 400 rows.

  • 1
    Let's do a quick test: check performance by forcing a different query plan, use SELECT STRAIGHT_JOIN on the very same query and, alternatively, use FORCE INDEX(campaign_idx) after the FROM Table1. – jynus Jul 6 '14 at 20:30

Here are my notices:

  1. Make sure that creator exists only in table1, or add [... WHERE table1.creator=..]
  2. It could be normal that the index on table2 is not used, specially if the majority or rows have the same value of the key.
  3. From the profiling, sending data is the longest status. This means that the amount of data the query returns is huge (You mentioned 'long list of fields..'). Try to execute the query by selecting one field only, or be select count(*) and see if there is a significant change
  4. Note that 400*270=108000, which is not a tiny number of scanned rows :)

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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