It looks like you are missing a 'GROUP BY' clause. But my question
is why are you even selecting from the itemlist table? If every
itemid in ratinglist is in itemlist (Does your database do
foreign key constraints?), then there's no reason to
join to item list. And that alone could be killing your query
because there is no really good way for a SQL optimizer to handle
that query.
Just select from ratinglist without the
join, try the select once with just an index on ratinglist.itemid,
once with an index on category, and once with just a
composite index on categoryid, itemid (this seems to be the ideal index for this select, but might be unneccessary).
Or create all the indexes, do an explain plan, and see which
index is selected.
For the composite index, you might have to include category
in both the select and the group by clause. Then again, the
order by may be killing your query in which case there's not
a whole lot SQL wise you can do about it, though maybe from
a database tuning perspective you can.
Update: In the case that you do need some fields
from itemlist, I'd consider making it a separate sql statement
('select ..stuff.. where itemid = ?') rather
than a join, it may or may not improve things. | [reply] |
I've omitted the SQL portions that grab several other fields from the item table at the same time as sorting
on the second table, as to prepare all this for perl and HTML output. I know that if I just wanted the average, I don't even need to JOIN.
Dr. Michael K. Neylon - mneylon-pm@masemware.com
||
"You've left the lens cap of your mind on again, Pinky" - The Brain
| [reply] |