mysql queries crawl when switching servers

Question

I ran into a problem last week moving from dev-testing where one of my quieries which had run perfectly in dev, was crawling on my testing server. It was fixed by adding FORCE INDEX on one of the indexes in the query.

Now i've loaded the same database into the production server (and it's running with the FORCE INDEX command, and it has slowed again. Any idea what would cause something like this to happen? The testing and prod are both running the same OS and version of mysql (unlike the dev).

Here's the query and the explain from it.

 EXPLAIN SELECT showsdate.bid, showsdate.bandid, showsdate.date, showsdate.time,
    -> showsdate.title, showsdate.name, showsdate.address, showsdate.rank, showsdate.city, showsdate.state,
    ->  showsdate.lat, showsdate.`long` , tickets.link, tickets.lowprice, tickets.highprice, tickets.source
    -> , tickets.ext, artistGenre, showsdate.img
    -> FROM tickets
    -> RIGHT OUTER JOIN (
    -> SELECT shows.bid, shows.date, shows.time, shows.title, artists.name, artists.img, artists.rank, artists
    -> .bandid, shows.address, shows.city, shows.state, shows.lat, shows.`long`, GROUP_CONCAT(genres.genre SEPARATOR
    ->  ' | ') AS artistGenre
    -> FROM shows FORCE INDEX (biddate_idx)
    -> JOIN artists ON shows.bid = artists.bid JOIN genres ON artists.bid=genres.bid
    -> WHERE  `long` BETWEEN -74.34926984058 AND -73.62463215942 AND lat BETWEEN 40.39373515942 AND 41.11837284058
    ->  AND shows.date >= '2009-03-02'  GROUP BY shows.bid, shows.date ORDER BY  shows.date, artists.rank  DESC
    ->  LIMIT 0, 30
    -> )showsdate ON showsdate.bid = tickets.bid AND showsdate.date = tickets.date;
+----+-------------+------------+--------+---------------+-------------+---------+------------------------------+--------+----------------------------------------------+
| id | select_type | table      | type   | possible_keys | key         | key_len | ref                          | rows   | Extra                                        |
+----+-------------+------------+--------+---------------+-------------+---------+------------------------------+--------+----------------------------------------------+
|  1 | PRIMARY     |  | ALL    | NULL          | NULL        | NULL    | NULL                         |     30 |                                              |
|  1 | PRIMARY     | tickets    | ref    | biddate_idx   | biddate_idx | 7       | showsdate.bid,showsdate.date |      1 |                                              |
|  2 | DERIVED     | genres     | index  | bandid_idx    | bandid_idx  | 141     | NULL                         | 531281 | Using index; Using temporary; Using filesort |
|  2 | DERIVED     | shows      | ref    | biddate_idx   | biddate_idx | 4       | activeHW.genres.bid          |      5 | Using where                                  |
|  2 | DERIVED     | artists    | eq_ref | bid_idx       | bid_idx     | 4       | activeHW.genres.bid          |      1 |                                              |
+----+-------------+------------+--------+---------------+-------------+---------+------------------------------+--------+----------------------------------------------+

Answer 1

I think I chimed in when you asked this question about the differences in dev -> test.

Have you tried rebuilding the indexes and recalculating statistics? Generally, forcing an index is a bad idea as the optimizer usually makes good choices as to which indexes to use. However, that assumes that it has good statistics to work from and that the indexes aren't seriously fragmented.

ETA:

To rebuild indexes, use:

REPAIR TABLE tbl_name QUICK;

To recalculate statistics:

ANALYZE TABLE tbl_name;

Answer 2

Does test server have only 10 records and production server 1000000000 records? This might also cause different execution times

Answer 3

Are the two servers configured the same? It sounds like you might be crossing a "tipping point" in MySQL's performance. I'd compare the MySQL configurations; there might be a memory parameter way different.