Post reply

Single-column index versus multi-column index

  • August 30, 2005 at 6:45 pm

    #87460

    Hi,

    I'm trying to optimize the following SELECT statement, and I'm trying to understand, among other things, whether to use single-column indexes or multi-columns indexes. 

    For exmaple, for the PCAPostWord table, should I have an index for the WordID column, and another index for the ThreadID column, or should I have a multi-column index that covers both the WordID column and the ThreadID column?

    Thanks

    Robert

    SELECT Thread.URL, Thread.ForumName, Category.Category, Thread.Subject, Thread.FirstPost

    FROM PCAThread Thread, PCAPostWord PostWord, PCAWord W, PCACategory Category

    WHERE

     W.Word     = List.SearchWord AND

     W.ID      = PostWord.WordID AND

     Thread.ID   = PostWord.ThreadID AND

     Category.ID = Thread.CategoryID

    GROUP BY Thread.URL, Thread.ForumName, Category.Category, Thread.Subject, Thread.FirstPost

    HAVING (COUNT(*) >= 2)

  • August 30, 2005 at 8:12 pm

    #586332

    Hi again,

    A followup question:  In general, with the given SELECT statement from the previous post, what are some good guidelines to optimize that query?  Should I just put single-column indexes on all the columns in the WHERE clause?

    Thanks again.

    Robert

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply