Query runs a long time in some newer MySQL versions

I have created a database on MySQL 5.0.15. I have a query and when I run this query on this MySQL version, I get 0.9 s run time. When I import this database to another MySQL server with same hardware and run the same query I get over 120s and sometimes MySQL hangs.

What is the difference between 5.0 and 5.1 or 5.5? I have tested 5.1 and 5.5 versions.

Is it possible a query takes longer in a newer version (something like mysql structure change)?

Sorry but I can't put this query here, but the query is like:

```
SELECT fl_passenger_ticket. *,
fl_aganc.name AS agancname,
fl_pnr.remark AS remark,
fl_pnr.reservetime AS reservetime,
fl_pnr.cancelpnr,
fl_flight_date.fromcity AS fromcity,
fl_flight_date.tocity AS tocity,
fl_flight_date.flightdate AS flightdate,
fl_flightdate_capacity.adultper AS adultper,
fl_flightdate_capacity.childper AS childper,
fl_flightdate_capacity.infantper AS infantper,
fl_flightdate_capacity.cancel AS cancelsegment,
fl_flightdate_capacity.tax1adultpric,
fl_flightdate_capacity.tax1childpric,
fl_flightdate_capacity.tax1infantpric,
fl_flightdate_capacity.tax2adultpric,
fl_flightdate_capacity.tax2childpric,
fl_flightdate_capacity.tax2infantpric,
( fl_flightdate_capacity.tax3adultpric +
fl_flightdate_capacity.tax4adultpric +
fl_flightdate_capacity.tax5adultpric ) AS taxxtadultpric,
( fl_flightdate_capacity.tax3childpric +
fl_flightdate_capacity.tax4childpric +
fl_flightdate_capacity.tax5childpric ) AS taxxtchildpric,
( fl_flightdate_capacity.tax3infantpric +
fl_flightdate_capacity.tax4infantpric
+
fl_flightdate_capacity.tax5infantpric ) AS taxxtinfan

Just off the bat, newer versions of MySQL actually improve innodb performance (especially 5.5). I would highly recommend updating to this version if you're going to run InnoDB.

One method you could use to hunt down why it's taking so much longer is using MySQL Profiles

mysql> SET PROFILING=1;
mysql> SHOW TABLES;
mysql> SELECT * FROM foo;
mysql> SET PROFILING=0;
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW PROFILES;
+----------+------------+-------------------+
| Query_ID | Duration   | Query             |
+----------+------------+-------------------+
|        1 | 0.09270400 | SHOW TABLES       |
|        2 | 0.00026400 | SELECT * FROM foo |
+----------+------------+-------------------+
2 rows in set (0.05 sec)

mysql> SHOW PROFILE FOR QUERY 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000053 |
| checking permissions | 0.000009 |
| Opening tables       | 0.000032 |
| System lock          | 0.000010 |
| init                 | 0.000028 |
| optimizing           | 0.000003 |
| statistics           | 0.000012 |
| preparing            | 0.000008 |
| executing            | 0.000003 |
| Sending data         | 0.000068 |
| end                  | 0.000004 |
| query end            | 0.000007 |
| closing tables       | 0.000008 |
| freeing items        | 0.000013 |
| logging slow query   | 0.000003 |
| cleaning up          | 0.000003 |
+----------------------+----------+
16 rows in set (0.04 sec)

This should give you an indication of where it's hanging. From your explain output, you should try to get some indexing on the second and third tables instead of doing full table scans. But without DDL or the actual join columns, I can't suggest anything better than to research indexing strategies.

StackExchange Database Administrators Q#6526, answer score: 10