Re: SQL Performance Help

Tony Grimes wrote:

I'm developing a course calendar for a client and I'm running into performance problems with the admin site. For example, when I try to include registration counts in the course list, the page really slows down for large course lists (50 or so):

COURSE        ATTENDEES      CAPACITY    SEATS LEFT
======        =========      ========    ==========
Course 1         5              10           5
Course 2         6              15           9
Course 3         4              10           6

I've been using one query to retrieve the course list and then one for each attendee count. Is there a more efficient way of doing this all in one query? I was thinking something like this (I'm not a SQL expert, so I don't know if this is even possible): SELECT

    course_name,
    capacity,
    count(query here) as attendee_count

FROM events AS e LEFT OUTER JOIN event_attendees AS a ON e.event_id = a.event_id WHERE start_time BETWEEN point_a AND point_b Or should I just pull everything as a separate row like this and sort it all out programmatically: SELECT

    e.course_name,
    e.capacity,
    a.user_id

FROM events AS e LEFT OUTER JOIN event_attendees AS a ON e.event_id = a.event_id WHERE start_time BETWEEN point_a AND point_b Or should I just try caching the data in PHP? Would an index help?

Index your tables, make the database do the work. Much easier and less prone to bugs :) Check you have an index on: events(event_id) event_attendees(event_id) table(start_time) (whichever that table applies to - I assume it's events). Maybe try a multi-column index if this query gets run a lot: create index event_eventid_start_time on events(event_id, start_time); Use 'explain' to see which one is being used and possibly get rid of the other one. I have a guide about how to index databases here: http://www.designmagick.com/article/16/ (Yes it's a postgresql site but the same rules apply to mysql and other databases as well). -- Postgresql & php tutorials http://www.designmagick.com/