[PHP-DB] SQL Performance Help

[Tony Grimes]

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

[PHP-DB] This won't work in MySQL 4.1.10

[Peter Karlsson]

 but it runs fine in 4.1.20. In .10 I get an error message about  
unknown column c. I built the sql with help from the comments from  
the MySQL website manual and have to admit that I don't fully  
understand it all ;). Is there a way to  solve this without upgrading  
the server?



SELECT DISTINCT O.id, O.header, O.state, O.prio, O.publication_date,  
O.user_id, MATCH (C.body) AGAINST ('mysearch') AS relevance FROM  
texts AS C JOIN jobs AS O ON C.job_id=O.id WHERE O.paper_id = 1 AND  
MATCH (c.body) AGAINST('+mysearch' IN BOOLEAN MODE) HAVING relevance  
 0.2 ORDER BY relevance DESC


Best regards,

Peter

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

RE: [PHP-DB] SQL Performance Help

[Bastien Koert]

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