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

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 2

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.