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 51

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.
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!


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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
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…

749 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