9 Added mysql-5.5, mysql-5.1, and mysql-5.0 tags since the three versions are compared
| link
    Tweeted twitter.com/#!/StackDBAs/status/121842866623483904
8 A little bit of formating to make it easier to understand
source | link
SELECT fl_passenger_ticket. * , 
       fl_aganc.Namename AS AgancName                           AS agancname, 
       fl_pnr.Remarkremark AS Remark                           AS remark, 
       fl_pnr.ReserveTimereservetime AS ReserveTime                      AS reservetime, 
       fl_pnr.CancelPnr cancelpnr, 
       fl_flight_date.FromCityfromcity As FromCity                 AS fromcity, 
       fl_flight_date.ToCitytocity As ToCity                   AS tocity, 
       fl_flight_date.FlightDateflightdate AS FlightDate               AS flightdate, 
       fl_flightdate_capacity.AdultPeradultper AS AdultPer         AS adultper, 
       fl_flightdate_capacity.ChildPerchildper AS ChildPer         AS childper, 
       fl_flightdate_capacity.InfantPerinfantper AS InfantPer        AS infantper, 
       fl_flightdate_capacity.Cancelcancel AS CancelSegment           AS cancelsegment, 
       fl_flightdate_capacity.Tax1AdultPric tax1adultpric, 
       fl_flightdate_capacity.Tax1ChildPric tax1childpric, 
       fl_flightdate_capacity.Tax1InfantPric tax1infantpric, 
       fl_flightdate_capacity.Tax2AdultPric tax2adultpric, 
       fl_flightdate_capacity.Tax2ChildPric tax2childpric, 
       fl_flightdate_capacity.Tax2InfantPric tax2infantpric, 
       ( fl_flightdate_capacity.Tax3AdultPrictax3adultpric + 
         fl_flightdate_capacity.Tax4AdultPrictax4adultpric + 
         fl_flightdate_capacity.Tax5AdultPrictax5adultpric ) AS TaxXtAdultPricAS taxxtadultpric, 
       ( fl_flightdate_capacity.Tax3ChildPrictax3childpric + 
         fl_flightdate_capacity.Tax4ChildPrictax4childpric + 
         fl_flightdate_capacity.Tax5ChildPrictax5childpric ) AS TaxXtChildPricAS taxxtchildpric, 
       ( fl_flightdate_capacity.Tax3InfantPrictax3infantpric + 
         fl_flightdate_capacity.Tax4InfantPrictax4infantpric 
         + 
         fl_flightdate_capacity.Tax5InfantPrictax5infantpric ) AS TaxXtInfantPrictaxxtinfantpric 
FromFROM 
   fl_passenger_ticket  
 inner Join     INNER JOIN fl_pnr 
         ON
  ( fl_passenger_ticket.PnrIDpnrid = fl_pnr.PnrIDpnrid ) inner 
 join      INNER JOIN fl_aganc 
         ON ( fl_pnr.AgancIDagancid = fl_aganc.AgancIDagancid ) 
       LEFT joinJOIN fl_flightdate_capacity  ON
         ON ( fl_pnr.PnrIDpnrid = fl_flightdate_capacity.PnrIDpnrid ) 
       LEFT joinJOIN fl_flight_date 
         ON ( fl_flightdate_capacity.FlightDateIDflightdateid = fl_flight_date.FlightDateIDflightdateid 
            ) 
WHERE 
  fl_passenger_ticket.TicketNumberticketnumber <> '' 
       AND fl_passenger_ticket.PnrIDpnrid <> 0 
       AND fl_pnr.AgancIDagancid = 60 
       AND fl_flightdate_capacity.AgancCharterIDaganccharterid = 0 
       AND fl_flightdate_capacity.Cancelcancel inIN ( 0 , 1 ) 
       AND fl_pnr.ReserveTimereservetime >= '2011/09/01 00:00:00' 
       AND fl_pnr.ReserveTimereservetime <= '2011/09/19 23:59:00' 
ORDER  BY
  fl_passenger_ticket.RowID rowid, 
          fl_pnr.reserveTimereservetime 
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
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 
7 added 1696 characters in body
source | link

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 f3fl_passenger_ticket. * , f1fl_aganc.Name asAS aAgancName , 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 ,
(f2 fl_flightdate_capacity.fTax3AdultPric + f2fl_flightdate_capacity.fTax4AdultPric + f2fl_flightdate_capacity.fTax5AdultPric ) asAS bTaxXtAdultPric , ( fl_flightdate_capacity.Tax3ChildPric + fl_flightdate_capacity.Tax4ChildPric + fl_flightdate_capacity.Tax5ChildPric FROM) table1AS TaxXtChildPric ,
( INNERfl_flightdate_capacity.Tax3InfantPric JOIN+ table2fl_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 JOINjoin tablefl_flightdate_capacity 3 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

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 f3.*, f1 as a, (f2.f + f2.f + f2.f) as b,... FROM table1
 INNER JOIN table2 ON ... 
  LEFT JOIN table 3 ON ... 
  WHERE ..... 
  ORDER BY ...
  • 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

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

6 added 113 characters in body
source | link
5 added 19 characters in body
source | link
4 added 19 characters in body
source | link
3 formatting, grammar, title clarification
source | link
2 formatting, grammar, title clarification
source | link
1
source | link