Mysql query, seems to timeout

Posted on 2010-11-29
Last Modified: 2012-05-10

I have a query but when it's run, it gives no error and doesn't seem to come back with any results. I left it running for 4 days and still no results.

In my opinion, the query doesn't seem to be that difficult or large and I'm not working with millions of records. Can anyone shed some light or maybe rewrite the query so it works?

The Tables:

ATL_Users -> list of users, ~4,500
ATL_V2_Affiliates -> list of affiliates, ~450
ATL_Hotels -> list of hotels, ~50
ATL_Rooms -> list of rooms, ~500
ATL_RoomBooking -> links Room to User, ~5,000
ATL_Flights -> list of flights, ~2,000
ATL_PackagesContents -> list of flights in a package, ~2,500
ATL_PackageBooking -> links PackageContents to User, ~1,500

Baically, I want to show first room booked date, last room booked date, last flight date / details (outbound), and first flight date / details (inbound) per user.

Many thanks,


DATE_FORMAT(InF.CheckinDateAndTime, '%H:%i') as InDCheckinDateAndTime,
DATE_FORMAT(InF.DepartDateAndTime, '%d-%b %H:%i') as InDDateAndTime,
DATE_FORMAT(InF.ArrivalDateAndTime, '%d-%b %H:%i') as InADateAndTime,
DATE_FORMAT(OutF.CheckinDateAndTime, '%H:%i') as OutDCheckinDateAndTime,
DATE_FORMAT(OutF.DepartDateAndTime, '%d-%b %H:%i') as OutDDateAndTime,
DATE_FORMAT(OutF.ArrivalDateAndTime, '%d-%b %H:%i') as OutADateAndTime,
DATE_FORMAT(BeginR.Date, '%d-%b') as BeginDate,
DATE_FORMAT(EndR.Date, '%d-%b') as EndDate,
BeginH.Name AS HotelName
ATL_Users AS U
INNER JOIN ATL_V2_Affiliates AS A ON U.ID_Affiliate = A.ID_Affiliate
INNER JOIN ATL_PackageBooking AS PB ON PB.ID_User = U.ID_User AND (U.JobNumber='CDATL002' or U.JobNumber='ALL') AND U.RegStatus='Registered'

((ATL_Packages AS InP
INNER JOIN ATL_PackageContents AS InPC ON InP.ID_Package = InPC.ID_Package AND InPC.JobNumber='CDATL002' AND InP.FlightsRequired<>'NoFlightsRequired'
INNER JOIN ATL_Flights AS InF ON InF.ID_Flight = InPC.ID_Flight AND InF.Direction='Inbound')
LEFT JOIN (ATL_PackageContents AS InPC2 
INNER JOIN ATL_Flights AS InF2 ON InF2.ID_Flight = InPC2.ID_Flight AND InF2.Direction='Inbound') ON InPC.ID_Package = InPC2.ID_Package AND InF.DepartDateAndTime > InF2.DepartDateAndTime) ON PB.ID_Package = InPC.ID_Package AND InPC2.ID_Package IS NULL
((ATL_Packages AS OutP
INNER JOIN ATL_PackageContents AS OutPC ON OutP.ID_Package = OutPC.ID_Package AND OutPC.JobNumber='CDATL002' AND OutP.FlightsRequired<>'NoFlightsRequired'
INNER JOIN ATL_Flights AS OutF ON OutF.ID_Flight = OutPC.ID_Flight AND OutF.Direction='Outbound')
LEFT JOIN (ATL_PackageContents AS OutPC2 
INNER JOIN ATL_Flights AS OutF2 ON OutF2.ID_Flight = OutPC2.ID_Flight AND OutF2.Direction='Outbound') ON OutPC.ID_Package = OutPC2.ID_Package AND OutF.ArrivalDateAndTime < OutF2.ArrivalDateAndTime) ON PB.ID_Package = OutPC.ID_Package AND OutPC2.ID_Package IS NULL
((ATL_RoomBooking AS BeginRB
INNER JOIN ATL_Rooms AS BeginR ON BeginRB.ID_Room = BeginR.ID_Room
INNER JOIN ATL_Hotels AS BeginH ON BeginR.ID_Hotel = BeginH.ID_Hotel AND (BeginH.HotelRequired<>'NoHotelRequired' or BeginH.HotelRequired is null))
LEFT JOIN (ATL_RoomBooking AS BeginRB2
INNER JOIN ATL_Rooms AS BeginR2 ON BeginR2.ID_Room = BeginRB2.ID_Room) ON BeginRB.ID_User = BeginRB2.ID_User AND BeginR.Date < BeginR2.Date) ON U.ID_User = BeginRB.ID_User AND BeginR2.ID_Room IS NULL
((ATL_RoomBooking AS EndRB
INNER JOIN ATL_Rooms AS EndR ON EndRB.ID_Room = EndR.ID_Room
INNER JOIN ATL_Hotels AS EndH ON EndR.ID_Hotel = EndH.ID_Hotel AND (EndH.HotelRequired<>'NoHotelRequired' or EndH.HotelRequired is null))
LEFT JOIN (ATL_RoomBooking AS EndRB2
INNER JOIN ATL_Rooms AS EndR2 ON EndR2.ID_Room = EndRB2.ID_Room) ON EndRB.ID_User = EndRB2.ID_User AND EndR.Date > EndR2.Date) ON U.ID_User = EndRB.ID_User AND EndR2.ID_Room IS NULL


Open in new window

Question by:marty_t
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3

Author Comment

ID: 34230523
Additionally, I can take out any "block" (i.e. the EndRB block, or the OutF block) and the query works.

If I leave them all as above, the MySQL "state" says statistics, and never gets any further.

Help. Thanks, Marty
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 34230635
you should make it simple...

select some_columns_here, max(some_column_here), min(some_column_here)...
from tablea a,tableb b, tablec c
where and
group by some_columns_here

I dont understand why so many joins you have in ypur post!
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34230651
what is the explain plan for the query?

apart from that, I presume I would try to reduce the 2 joins for the same table into 1 join, a group by, and some CASE structures to format the output.
Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.


Author Comment

ID: 34231101
I tried an explain, but that too hangs on "statistics".

I would do a group by, but I need other items from the tables of first dates, etc (i.e. flight names)

I found this as a bug in MySQL can someone comment on if this is possibly what is happening and if so, where can I IGNORE INDEXES?



Author Comment

ID: 34231112
Explain with 1 block removed...

1	SIMPLE	InPC	ALL					2012	Using where; Using temporary; Using filesort
1	SIMPLE	InP	eq_ref	PRIMARY,ID_Package,ID_Package_2	PRIMARY	8	h1249406.InPC.ID_Package	1	Using where
1	SIMPLE	OutP	eq_ref	PRIMARY,ID_Package,ID_Package_2	PRIMARY	8	h1249406.InPC.ID_Package	1	Using where
1	SIMPLE	InF	eq_ref	PRIMARY,ID_Flight,ID_Flight_2	PRIMARY	8	h1249406.InPC.ID_Flight	1	Using where
1	SIMPLE	PB	ALL					1472	Using where; Using join buffer
1	SIMPLE	InPC2	ALL					2012	Using where
1	SIMPLE	InF2	eq_ref	PRIMARY,ID_Flight,ID_Flight_2	PRIMARY	8	h1249406.InPC2.ID_Flight	1	
1	SIMPLE	OutPC	ALL					2012	Using where; Using join buffer
1	SIMPLE	OutF	eq_ref	PRIMARY,ID_Flight,ID_Flight_2	PRIMARY	8	h1249406.OutPC.ID_Flight	1	Using where
1	SIMPLE	OutPC2	ALL					2012	Using where
1	SIMPLE	OutF2	eq_ref	PRIMARY,ID_Flight,ID_Flight_2	PRIMARY	8	h1249406.OutPC2.ID_Flight	1	
1	SIMPLE	U	eq_ref	PRIMARY,ID_User,ID_User_2	PRIMARY	8	h1249406.PB.ID_User	1	Using where
1	SIMPLE	A	eq_ref	PRIMARY,ID_Affiliate,ID_Affiliate_2	PRIMARY	8	h1249406.U.ID_Affiliate	1	
1	SIMPLE	BeginRB	ALL					5108	Using where; Using join buffer
1	SIMPLE	BeginR	eq_ref	PRIMARY,ID_Rooms,ID_Rooms_2	PRIMARY	8	h1249406.BeginRB.ID_Room	1	
1	SIMPLE	BeginH	eq_ref	PRIMARY,ID_Hotel,ID_Hotel_2	PRIMARY	8	h1249406.BeginR.ID_Hotel	1	Using where
1	SIMPLE	BeginRB2	ALL					5108	
1	SIMPLE	BeginR2	eq_ref	PRIMARY,ID_Rooms,ID_Rooms_2	PRIMARY	8	h1249406.BeginRB2.ID_Room	1	Using where

Open in new window

LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 34231119
visibly you have no proper indexes for the joins at all?

Author Comment

ID: 34231246
Proper indexes?

Each table has a primary key which is a unique index (according to Navicat)
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 500 total points
ID: 34232358
seems you need to read up about indexes ..

just a PK is far from enough.
for example:
INNER JOIN ATL_PackageBooking AS PB ON PB.ID_User = U.ID_User AND (U.JobNumber='CDATL002' or U.JobNumber='ALL') AND U.RegStatus='Registered'

Open in new window

You have surely a primary key on that table, but thats not used in your join condition.

a index on JobNumber + ID_User + RegStatus will help, for that join.
please create such a index (1 index for all 3 columns, not 3 indexes for 1 column each !), and then check the explain plan again.

Author Comment

ID: 34237390
I have sorted it, using the explain, I've indexed everything that was showing as blank for "key".

Now, every line is using an index and the report works.

Many thanks for your help. It's wierd that there was no error though, even a timeout error.

Featured Post

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
CRM Online Report - Passing Parameter from Opportunity to Report 1 49
Format Transaction Number 19 50
T-SQL: Please describe what a page split is 5 58
dat and idx extensions 11 43
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question