Query runs a long time in some newer MySQL versions

Question

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 taxxtinfantpric 
FROM   fl_passenger_ticket 
       INNER JOIN fl_pnr 
         ON ( fl_passenger_ticket.pnrid = fl_pnr.pnrid ) 
       INNER JOIN fl_aganc 
         ON ( fl_pnr.agancid = fl_aganc.agancid ) 
       LEFT JOIN fl_flightdate_capacity 
         ON ( fl_pnr.pnrid = fl_flightdate_capacity.pnrid ) 
       LEFT JOIN fl_flight_date 
         ON ( fl_flightdate_capacity.flightdateid = fl_flight_date.flightdateid 
            ) 
WHERE  fl_passenger_ticket.ticketnumber <> '' 
       AND fl_passenger_ticket.pnrid <> 0 
       AND fl_pnr.agancid = 60 
       AND fl_flightdate_capacity.aganccharterid = 0 
       AND fl_flightdate_capacity.cancel IN ( 0, 1 ) 
       AND fl_pnr.reservetime >= '2011/09/01 00:00:00' 
       AND fl_pnr.reservetime <= '2011/09/19 23:59:00' 
ORDER  BY fl_passenger_ticket.rowid, 
          fl_pnr.reservetime 

• I have 4 joins.

• The table is innodb.

• There are 100000 records

The result is 100 rows and 50 columns.

EXPLAIN result is

show variables like 'innodb%' result

Answer 1

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.

Answer 2

Using MySQL 5.5 out-of-the-box without proper configuration is like getting a Lamborghini and expecting topnotch performance on a gallon of regular gasoline (87 Octane).

You should expect better performance with high octane gasoline in a Lamborghini.

As with any database product, it is only as performance-enhanced as you actually configure it. Just like Spiderman said (8:36 - 8:40) : "WITH GREAT POWER, THERE MUST ALSO ALWAYS BE GREAT RESPONSIBILITY".

To get better performance out of MySQL 5.5, you must honestly configure certain things.

• OPTION 1 : Use separate tablespace files for InnoDB, separating data and index pages from ibdata1

• OPTION 2 : Configure options that engage more CPUs

• OPTION 3 : Increase the number of threads dedicated for reads and writes

• OPTION 4 : Properly tune InnoDB Options

• OPTION 5 : Size your InnoDB Buffer Pool so it is large enough to hold all data, or 75% of installed RAM, whichever is smaller.

• OPTION 6 : Use O_DIRECT for InnoDB Flushing

• OPTION 7 : Use a dedicated DB Server, apart from LAMP, WAMP, XAMPP, Munin, and software like these.