Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Location
    Toronto
    Posts
    33

    Question Unanswered: How many indexes do I need to create?

    My SELECT uses 4 tables, and goes something like this:

    SELECT field1, field_2 field_20
    FROM tbl_1
    LEFT JOIN tbl_2 on (tbl_2.field_X=tbl_1.field_A)
    LEFT JOIN tbl_3 on (tbl_3.field_Y=tbl_2.field_B)
    LEFT JOIN tbl_4 on (tbl_4.field_Z=tbl_3.field_C)
    WHERE
    (tbl_1.status=1) AND
    (tbl_1.field_3= & val1 & ) AND
    (tbl_2.field_4= & val2 & ) AND
    (tbl_2.field_5= & val3& ) AND
    (tbl_1.startDate> & dateVal1 & )
    GROUP BY
    tbl_1. field1
    ORDER BY
    tbl_1.startDate, tbl_1.startTime

    The above is a search query in a web application. The user has 9 search fields to pick from. Most of these fields are optional, so the WHERE could have only 1 or 2 criteria, or it could have 9.

    My Question:
    Regarding performance and indexes: Since there are 9 search fields, do I need to create one index for every possible combination search fields used? There are too many possibilities / column combinations!! This is a search query that is performed VERY OFTEN. Updates will be performed just a few times an hour. Number of rows will grow to 10s of thousands.

    Thx!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    start by creating an index on every join column that isn't already a primary key

    do an EXPLAIN

    then consider adding indexes on columns used in the WHERE clause (one index per column)

    do an EXPLAIN

    then consider adding indexes on your sort columns, or better yet, don't sort

    do an EXPLAIN

    finally, lose the GROUP BY clause
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •