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.

In reply to Re: Re: (OT) Improving a SQL JOIN statement by runrig
in thread (OT) Improving a SQL JOIN statement by Masem

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.