can I increase performance with another index?

gomer · #1 (**permalink**) 01-29-04, 21:22

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.

Thanks.
-gomer

r937 · #2 (**permalink**) 01-29-04, 21:31

Quote:

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).

-- http://www.mysql.com/doc/en/MySQL_indexes.html

so declare a separate index on column_2

gomer · #3 (**permalink**) 01-29-04, 23:02

r937, thanks so much for a clear answer, it is very much appreciated!

-gomer

Internet Services

Web Development

Digital Marketing

Consumer Tech