Solved

Mysql query, seems to timeout

Posted on 2010-11-29
9
473 Views
Last Modified: 2012-05-10
Hi,

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,

Marty

SELECT DISTINCT
U.ID_User,
U.ID_Affiliate,
A.FriendlyName,
U.Title,
U.Firstname,
U.Surname,
U.Mobile,
U.DelType,
InF.DepartAirport,
InF.DepartTerminal,
InF.FlightNumber,
InF.ArrivalAirport,
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,
OutF.DepartAirport,
OutF.DepartTerminal,
OutF.FlightNumber,
OutF.ArrivalAirport,
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
FROM
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'

INNER JOIN 
((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
		
INNER JOIN 
((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
		
INNER JOIN
((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
		
INNER JOIN
((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

ORDER BY
U.Surname;

Open in new window

0
Comment
Question by:marty_t
  • 5
  • 3
9 Comments
 
LVL 1

Author Comment

by:marty_t
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
0
 
LVL 51

Expert Comment

by:HainKurt
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 a.id=b.id and b.id=c.id
group by some_columns_here

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

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.
0
 
LVL 1

Author Comment

by:marty_t
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?

http://bugs.mysql.com/bug.php?id=20932

Thanks,

Marty
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
LVL 1

Author Comment

by:marty_t
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

0
 
LVL 142

Expert Comment

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

Author Comment

by:marty_t
ID: 34231246
Proper indexes?

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

Accepted Solution

by:
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.
0
 
LVL 1

Author Comment

by:marty_t
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.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
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…

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now