Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004

    Unanswered: can I increase performance with another index?

    I have a table with a composite primary key - for example the primary key consists of column_1 and column_2.

    If I am doing a select using only one of the columns, do I need a another index (in addition to the one for the primary key) to attain the maximum speed in a query such as the following:

    "SELECT FROM table WHERE column_2 = "x" ;"

    Will the index created for the primary key be sufficient to give me the best performance or can I increase the speed of the query by adding a separate index on column_2.


  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    MySQL can't use a partial index if the columns don't form a leftmost prefix of the index. Suppose you have the SELECT statements shown here:

    mysql> SELECT * FROM tbl_name WHERE col1=val1;
    mysql> SELECT * FROM tbl_name WHERE col2=val2;
    mysql> SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3;

    If an index exists on (col1, col2, col3), only the first of the preceding queries uses the index. The second and third queries do involve indexed columns, but (col2) and (col2, col3) are not leftmost prefixes of (col1, col2, col3).

    so declare a separate index on column_2 | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2004
    r937, thanks so much for a clear answer, it is very much appreciated!


Posting Permissions

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