Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Mysql query, seems to timeout

Posted on 2010-11-29
9
Medium Priority
?
491 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
[X]
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
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 59

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 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.
0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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
 
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 143

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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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

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.

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

688 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