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 enter image description here

show variables like 'innodb%' result

enter image description here

The first things I'll mention. Can you post the EXPLAIN output? EXPLAIN SELECT .... Next, verify the configuration of the new server is similar to the old server as far as innodb settings go.. innodb_buffer_pool for starters. Is the hardware similar (amount of RAM specifically)? Please add the answers to the question, if you would. — Derek Downey, Oct 05 '11 at 12:35
humm, I wouldn't point mysql version as the cause of this discrepant behavior since the difference is too noticeable. When you say another mysql server, you mean just another version of mysql or another hardware server? — marcio, Oct 05 '11 at 12:48
how does that output compare to the 5.0 version? What is the data size of your innodb tables? If it's bigger than 15MB, you should increase innodb_buffer_pool_size (requires access to the my.cnf and server restart) — Derek Downey, Oct 05 '11 at 13:11
inno db table related to query 5MB. i add mysql version 5 result soon. what shoud innodb_buffer_pool_size be? — user3731, Oct 05 '11 at 13:17
Are the table indexes all intact in the restored database? I have a feeling involving indexation. — marcio, Oct 05 '11 at 13:35
@av See innodb portion of this answer to calculate the size of your innodb_buffer_pool, with caveat that it shouldn't be more than 80% of your available memory...Allow for growth, but it looks like you don't need to touch it. — Derek Downey, Oct 05 '11 at 13:35
i update my question with original query. table havent any index. plz check my query for performance. — user3731, Oct 05 '11 at 15:59
Why the LEFT JOIN on fl_flightdate_capacity when there are two conditions in the WHERE? That makes it an INNER JOIN. Or you should make it part of the join condition — Frank Heikens, Oct 05 '11 at 16:20
Have you already compared both server configurations or checked what @DTest suggested? — marcio, Oct 05 '11 at 17:19
at last with index i have 0.2s run time in mysql 5.1. but without index run time is 120s.then why without index mysql 5.0 has a good run time? — user3731, Oct 05 '11 at 20:54

score 10 · Accepted Answer · answered Oct 05 '11 at 13:08

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.

thank u.with "some indexing on the second and third tables" i got under 1 s run time. but what about mysql 5.0 and good run time without index? — user3731, Oct 06 '11 at 05:55
@av First place I'd look is at your tmp_table_size and max_heap_table_size (the lesser of the 2 is the limit). If it your temporary table doesn't fit in amount specified by those values, it will create an on-disk temporary table. — Derek Downey, Oct 06 '11 at 12:36

score 8 · Answer 2 · edited May 23 '17 at 12:40

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.

Query runs a long time in some newer MySQL versions

2 Answers2

Linked