|
|
Posted by CJ Llewellyn on 05/27/05 01:25
On Thu, 26 May 2005 04:13:41 +0100, StealthBananaT wrote:
>
> I'd like to thank everybody for their help, but the problem remains. I've tried making reviews.filmID an index but that seemed to
> have no effect.
>
> I have a third table called users, and when I try to count the number of reviews submitted by users I have exactly the same
> problem...
LIMIT can be very ineffiecient at returning results, effectively.
In your case it'll have to calculate the number of reviews for 10,000
before returning 100 rows.
SELECT f.title, f.id, count( r.id )
FROM films f
LEFT JOIN reviews r ON ( f.id = r.filmid )
WHERE f.id > 0 AND f.id <= 100
GROUP BY f.id
Use the where clause for pagination, you'll find your system responds a
lot quicker ;-)
Navigation:
[Reply to this message]
|