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/
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
