Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 500
  • Last Modified:

Mysql query, seems to timeout

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
marty_t
Asked:
marty_t
  • 5
  • 3
1 Solution
 
marty_tAuthor Commented:
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
 
HainKurtSr. System AnalystCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
marty_tAuthor Commented:
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
 
marty_tAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
visibly you have no proper indexes for the joins at all?
0
 
marty_tAuthor Commented:
Proper indexes?

Each table has a primary key which is a unique index (according to Navicat)
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
marty_tAuthor Commented:
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now