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