Re: [PHP-DB] 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): COURSEATTENDEES CAPACITYSEATS 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
[PHP-DB] SQL Performance Help
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): COURSEATTENDEES CAPACITYSEATS 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? I realize any answers might be complicated, but if you could just point me in the right direction, I can probably figure the rest out. Thanks, Tony -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] SQL Performance Help
1. yes indexes could help, if mysql uses them. The mysql optimiser may or may not use the index for the query depending on the statement...it sounds like you are doing a full table scan on the data 2. there are two schools of thought here: a. run the whole thing as two statements (one outer loop to loop thru the course list, and an inner one to get the attendees) b. examine the join query to see if you are starting out in the correct way (mysql can be sensitive to the way the tables are joined), examine the indexes and use the EXPLAIN statement to see how the optimiser attempts the query. I would just test both ways and see which one performs better... Bastien From: Tony Grimes [EMAIL PROTECTED] To: PHP-DB php-db@lists.php.net Subject: [PHP-DB] SQL Performance Help Date: Wed, 27 Dec 2006 14:05:13 -0700 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): COURSEATTENDEES CAPACITYSEATS 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? I realize any answers might be complicated, but if you could just point me in the right direction, I can probably figure the rest out. Thanks, Tony -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php _ Enter the Telus Mobility Xbox a Day contest for your chance to WIN! Telus Mobility is giving away an Microsoft Xbox® 360 every day from November 20 to December 31, 2006! Just download Windows Live (MSN) Messenger to your IM-capable TELUS mobile phone, and you could be a winner! http://www.telusmobility.com/msnxbox/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php