MySQL performance with large tables

[rcouser]

Hi,

I need help with mysql performance with large tables. I have the following tables:

Person (8,021 Records)
---
ID (Primary Key)
Title (varchar)
FirstName (varchar)
Surname (varchar)
ContactDetails (text)
PersonCode (int)

Booking (79,378 Records)
---
ID (Primary Key)
StartDate (datetime)
FinishDate (datetime)
BookingCode (int)
RunCode (int)
BookingTypeCode (int)
PersonBooking (171,563)
---
ID (Primary Key)
PersonCode (int)
BookingCode (int)
IsDriver (varchar) 

 

It is taking 8.4983 sec just to display 31 results with the query below, anyone any idea how I could speed this up.

Kind Regards.

 

SELECT t1.BookingCode, t1.IsDriver, t2.RunCode, t2.Duration, t2.BookingTypeCode, DATE_FORMAT(t2.StartDate, '%a') AS StartDay, DATE_FORMAT(t2.StartDate, '%H:%i') AS StartTime FROM person_booking t1 
LEFT JOIN booking t2 ON t1.BookingCode = t2.BookingCode 
WHERE t1.PersonCode = 21280 
AND (t2.StartDate >= '2012-07-02' AND t2.StartDate < '2012-07-09') 
ORDER BY t2.StartDate 

[mikosiko]

show us the EXPLAIN PLAN,

 

what INDEXES you person_booking and booking tables have?... most likely you need to have indexes on the fields BookingCode of your table Bookings and in the fields PersonCode and BookingCode in your table PersonBooking... (it is named PersonBooking or person_booking?)...

 

in an additional observations:

- is there any reason for you to use an ID field in your table Booking?... is not enough to use just the BookingCode as PK?

- the ID field on the table PersonBooking could be dropped too

[Barand]

Are there records in person_bookings that don't have a matching booking? If not, use INNER JOIN instead of LEFT JOIN - they are much faster.

[jazzman1]

In addition, check for overhead values, if so "OPTIMIZE TABLE(s)" .

OPTIMIZE TABLE `tbl_name`