Speeding up a complicated multi-joins MySQL query (created via CakePHP if that matters)

I have a MySQL query below that I'm using to get events for my event listings page. The problem is, it's taking ~35 seconds to run with the limit 10, and another ~35 seconds to do the COUNT for pagination. 70+ seconds page-load time just won't cut it, as you can imagine. And this is only with 740 event results! I'm scared to think how this will run when we get 2000+.

We've tried indexing (to the best of our lacking index-knowledge), and that had literally zero effect.

Explanation of table associations: An Event can be held at either a restaurant or a venue. The City of that event is determined by the city_id of the Restaurant or Venue it's being held at. It's also getting Uploads (photos in this case).

The somewhat confusing part is the Schedule/Date - a Schedule(s) holds the start/end/repeat information for an event. The Date records are created based on the Schedule's information and holds an individual record for every day the event is being held (start = datetime, end = datetime)

I'm using CakePHP to create this query, and have listed my associations at the bottom:

SELECT
`Event`.*, `Venue`.`id`, `Venue`.`slug`, `Venue`.`name`, `Venue`.`GPS_Lon`,
`Venue`.`GPS_Lat`, `Venue`.`city_id`, `VenueCity`.`name`, `VenueCity`.`slug`,
`Restaurant`.`id`, `Restaurant`.`slug`, `Restaurant`.`name`, `Restaurant`.`GPS_Lat`,
`Restaurant`.`GPS_Lon`, `Restaurant`.`city_id`, `RestaurantCity`.`name`, 
`RestaurantCity`.`slug`, GROUP_CONCAT(Date.start, "|", Date.end

ORDER BY Date.start ASC SEPARATOR "||") AS EventDates
FROM `events` AS `Event`
LEFT JOIN restaurants AS `Restaurant` ON (`Restaurant`.`id` = `Event`.`restaurant_id`)
LEFT JOIN venues AS `Venue` ON (`Venue`.`id` = `Event`.`venue_id`)
LEFT JOIN cities AS `VenueCity` ON (`Venue`.`city_id` = `VenueCity`.`id`)
LEFT JOIN cities AS `RestaurantCity` ON (`Restaurant`.`city_id` = `RestaurantCity`.`id`)
INNER JOIN schedules AS `Schedule` ON (`Schedule`.`event_id` = `Event`.`id`)
INNER JOIN dates AS `Date` ON (`Date`.`schedule_id` = `Schedule`.`id`)
LEFT JOIN uploads AS `Upload` ON (`Upload`.`event_id` = `Event`.`id`)
WHERE `Event`.`approval_status_id` = 1 AND `Date`.`start` >= '2011-07-11 12:38:54'
GROUP BY `Event`.`id`
ORDER BY `Date`.`start` ASC LIMIT 10

CakePHP associations:

Event belongsTo Venue
Venue hasMany Event

Event belongsTo Restaurant
Restaurant hasmany Event

Event hasMany Upload
Upload belongsTo Event

City hasMany Restaurant
City hasMany Venue
Restaurant belongsTo City
Venue belongsTo City

Event hasMany Schedule
Schedule belongsTo Event
Schedule hasMany Date
Date belongsTo Schedule

UPDATE (per @Zoredache request):

This is what I get from adding EXPLAIN before the select:

开发者_Go百科id  select_type  table          type  possible_keys            key              key_len   ref                             rows  Extra
1   SIMPLE       Event          ref   PRIMARY,approval status  approval status  5         const                           946   Using where; Using temporary; Using filesort
1   SIMPLE       Restaurant     ref   PRIMARY,id               id               4         medut_ent.Event.restaurant_id   1 
1   SIMPLE       Venue          ref   PRIMARY,id               id               4         medut_ent.Event.venue_id        1 
1   SIMPLE       VenueCity      ref   PRIMARY,id               id               4         medut_ent.Venue.city_id         1 
1   SIMPLE       RestaurantCity ref   PRIMARY,id               id               4         medut_ent.Restaurant.city_id    1 
1   SIMPLE       Schedule       ref   PRIMARY,index            index            5         medut_ent.Event.id              1     Using where; Using index
1   SIMPLE       Date           ref   all cols,start...        all cols         5         medut_ent.Schedule.id           8     Using where; Using index
1   SIMPLE       Upload         ALL                                                                                       4240  

SELECT STRAIGHT_JOIN
`Event`.*, `Venue`.`id`, `Venue`.`slug`, `Venue`.`name`, `Venue`.`GPS_Lon`,
`Venue`.`GPS_Lat`, `Venue`.`city_id`, `VenueCity`.`name`, `VenueCity`.`slug`,
`Restaurant`.`id`, `Restaurant`.`slug`, `Restaurant`.`name`, `Restaurant`.`GPS_Lat`,
`Restaurant`.`GPS_Lon`, `Restaurant`.`city_id`, `RestaurantCity`.`name`, 
`RestaurantCity`.`slug`, GROUP_CONCAT(Date.start, "|", Date.end ORDER BY Date.start ASC SEPARATOR "||") AS EventDates

FROM `events` AS `Event`
INNER JOIN schedules AS `Schedule` ON (`Schedule`.`event_id` = `Event`.`id`)
INNER JOIN dates AS `Date` ON (`Date`.`schedule_id` = `Schedule`.`id`)
LEFT JOIN restaurants AS `Restaurant` ON (`Restaurant`.`id` = `Event`.`restaurant_id`)
LEFT JOIN cities AS `RestaurantCity` ON (`Restaurant`.`city_id` = `RestaurantCity`.`id`)
LEFT JOIN venues AS `Venue` ON (`Venue`.`id` = `Event`.`venue_id`)
LEFT JOIN cities AS `VenueCity` ON (`Venue`.`city_id` = `VenueCity`.`id`)
LEFT JOIN uploads AS `Upload` ON (`Upload`.`event_id` = `Event`.`id`)
WHERE `Event`.`approval_status_id` = 1 
AND `Date`.`start` >= '2011-07-11 12:38:54'
GROUP BY `Event`.`id`
ORDER BY `Date`.`start` ASC 
LIMIT 10

Assuming indexing is correct, try and move around some of your joins to first utilize those that are used in your WHERE clause while also making use of STRAIGHT_JOIN to ensure your orderings are not unduly optimized by MySQL:

SELECT STRAIGHT_JOIN
`Event`.*, `Venue`.`id`, `Venue`.`slug`, `Venue`.`name`, `Venue`.`GPS_Lon`,
`Venue`.`GPS_Lat`, `Venue`.`city_id`, `VenueCity`.`name`, `VenueCity`.`slug`,
`Restaurant`.`id`, `Restaurant`.`slug`, `Restaurant`.`name`, `Restaurant`.`GPS_Lat`,
`Restaurant`.`GPS_Lon`, `Restaurant`.`city_id`, `RestaurantCity`.`name`, 
`RestaurantCity`.`slug`, GROUP_CONCAT(Date.start, "|", Date.end ORDER BY Date.start ASC SEPARATOR "||") AS EventDates

FROM `events` AS `Event`
INNER JOIN schedules AS `Schedule` ON (`Schedule`.`event_id` = `Event`.`id`)
INNER JOIN dates AS `Date` ON (`Date`.`schedule_id` = `Schedule`.`id`)
LEFT JOIN restaurants AS `Restaurant` ON (`Restaurant`.`id` = `Event`.`restaurant_id`)
LEFT JOIN cities AS `RestaurantCity` ON (`Restaurant`.`city_id` = `RestaurantCity`.`id`)
LEFT JOIN venues AS `Venue` ON (`Venue`.`id` = `Event`.`venue_id`)
LEFT JOIN cities AS `VenueCity` ON (`Venue`.`city_id` = `VenueCity`.`id`)
LEFT JOIN uploads AS `Upload` ON (`Upload`.`event_id` = `Event`.`id`)
WHERE `Event`.`approval_status_id` = 1 
AND `Date`.`start` >= '2011-07-11 12:38:54'
GROUP BY `Event`.`id`
ORDER BY `Date`.`start` ASC 
LIMIT 10

You might also find it faster to just run a separate query for dates as opposed to the GROUP_CONCAT statement as it's possible this could be creating TEMP TABLES (which would be apparent in your EXPLAIN statement).

Try to get rid of that group_concat. The fact that you are using both a temporary table and a file sort is a sign.

You should also put indexes on all of your foreign keys, restaurant_id, venue_id, etc. These should include your local id second, so on RestaurantCity, on city_id andid`.

Turns out, the Upload table had no index. Simply adding an index to that made it run incredibly fast (142ms instead of 75000+ms).

I found the issue via EXPLAIN SELECT ...(thanks to @Zoredache) - details of the EXPLAIN in "UPDATE" of answer.