mysql explain different results on different servers, same query, same db

Question

After much work I finally got a rather complicated query to work very smootly and return results very quickly.

It was running well on both dev and testing, but now testing has slowed considerably. The explain query which takes 0.06 second on dev and was about the same in testing is now 7 seconds in testing.

The explains are slightly different, and I'm not sure why this would be The explain from dev

-+---------+------------------------------+------+------------------------------
---+
| id | select_type | table      | type   | possible_keys           | key
 | key_len | ref                          | rows | Extra
   |
+----+-------------+------------+--------+-------------------------+------------
-+---------+------------------------------+------+------------------------------
---+
|  1 | PRIMARY     |  | ALL    | NULL                    | NULL
 | NULL    | NULL                         |    5 |
   |
|  1 | PRIMARY     | tickets    | ref    | biddate_idx             | biddate_idx
 | 7       | showsdate.bid,showsdate.date |   78 |
   |
|  2 | DERIVED     | shows      | ALL    | biddate_idx,latlong_idx | NULL
 | NULL    | NULL                         | 3089 | Using temporary; Using fileso
rt |
|  2 | DERIVED     | genres     | ref    | bandid_idx              | bandid_idx
 | 4       | activehw.shows.bid           |    2 | Using index
   |
|  2 | DERIVED     | artists    | eq_ref | bid_idx                 | bid_idx
 | 4       | activehw.genres.bid          |    1 | Using where
   |
+----+-------------+------------+--------+-------------------------+------------

and in the testing

| id | select_type | table      | type   | possible_keys           | key         | key_len | ref                          | rows   | Extra                                        |
+----+-------------+------------+--------+-------------------------+-------------+---------+------------------------------+--------+----------------------------------------------+
|  1 | PRIMARY     |  | ALL    | NULL                    | NULL        |    NULL | NULL                         |      5 |                                              |
|  1 | PRIMARY     | tickets    | ref    | biddate_idx             | biddate_idx |       7 | showsdate.bid,showsdate.date |     78 |                                              |
|  2 | DERIVED     | genres     | index  | bandid_idx              | bandid_idx  |     139 | NULL                         | 531281 | Using index; Using temporary; Using filesort |
|  2 | DERIVED     | artists    | eq_ref | bid_idx                 | bid_idx     |       4 | activeHW.genres.bid          |      1 |                                              |
|  2 | DERIVED     | shows      | eq_ref | biddate_idx,latlong_idx | biddate_idx |       7 | activeHW.artists.bid         |      1 | Using where                                  |
+----+-------------+------------+--------+-------------------------+-------------+---------+------------------------------+--------+----------------------------------------------+
5 rows in set (6.99 sec)

The order of the tables is different, even though the queries are exactly the same. Is this what would cause the slowdown? if so, how would I fix it? The dev is windows, testing is centOs. both running same version of mysql 5.0, and like I said, testing was running perfectly and I haven't made any structural changes to the database.

I ran mysqlcheck and all tables came back ok.

Answer 1

I would try regenerating statistics and rebuilding the indexes for all the tables and see if that fixes your problem - it's likely that is why the plans would be different.

There are lots of other things it could be (memory, disk, os differences, other loads, etc) but I'm assuming those probably aren't the issue since you mentioned that it ran fine before.

Answer 2

MySQL looks at the data in the tables as well as the query itself to decide which execution plan to use.

If the data is the same in both databases, I'd suggest using ANALYZE or OPTIMIZE on all the tables in your query.

Answer 3

The first plan doesn't use index on shows.

If you are sure this index will help you, force it:

SELECT ...
FROM ..., shows FORCE INDEX (biddate_idx) , ...
WHERE ...

Meanwhile, collect statistics for your tables.

Answer 4

Are you sure these are from the same query? The explains aren't just slightly different, there are considerable differences between them:

1. The WHERE clause is hitting different tables (artists on dev, shows on testing)
2. The number of rows it's hitting in genres is different (2 on dev, 531281 on testing).
3. Other miscellaneous differences between the first and second explains (stuff in EXTRA mainly).

Answer 5

We just experienced a very similar problem with a newly built master taking several minutes to execute the same query that old master (with less power) completed in a fraction of a second. We ran repair table quick on two of the myisam tables in the query and now the new master executes the query at least as fast as the old one.

Thanks!