Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
1  
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 5 '11 at 12:35
    
where do check innodb_buffer_pool? hardware is same. –  a.v Oct 5 '11 at 12:39
    
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? –  marcioAlmada Oct 5 '11 at 12:48
    
i mean mysql version –  a.v Oct 5 '11 at 12:50
2  
@av show variables like 'innodb%' should help. –  Derek Downey Oct 5 '11 at 12:58
show 8 more comments

2 Answers 2

up vote 8 down vote accepted

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.

share|improve this answer
    
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? –  a.v Oct 6 '11 at 5: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 6 '11 at 12:36
add comment

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.

share|improve this answer
    
Very helpful Rolando! Thank you! –  Anthony Gatlin May 26 '12 at 14:26
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.