6

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

Improve this question
13
  • 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. Commented Oct 5, 2011 at 12:35
  • where do check innodb_buffer_pool? hardware is same. Commented Oct 5, 2011 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? Commented Oct 5, 2011 at 12:48
  • i mean mysql version Commented Oct 5, 2011 at 12:50
  • 2
    @av show variables like 'innodb%' should help. Commented Oct 5, 2011 at 12:58

2 Answers 2

Reset to default
10

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.

Improve this answer
2
  • 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? Commented Oct 6, 2011 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. Commented Oct 6, 2011 at 12:36
8

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.

Improve this answer
0

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.