CODEWITHSUNDEEP
mysql
Konrad
Konrad

Reputation: 941

Does creating an index on two columns x and y makes queries faster when searching on column x only?

Does creating an index on two columns 'x' and 'y' makes queries faster when searching on column x only? For example I have a table with columns 'ID', 'x' and 'y'. And I have two questions:

  1. When I create two separate indexes for 'x' and 'y', will it help (make things faster) with a query like this:

    select * from `table` where x=1 and y=2

? I believe it will not. But not sure.

  1. When I create one index on two colums 'x' and 'y' will it help with a query like:

    select * from `table` where x=1

? That is, searching only on one of these columns.

Upvotes: 1

Views: 57

Answers (1)

Ulrich Thomas Gabor
Ulrich Thomas Gabor

Reputation: 6654

  1. It might speed up the query. There exists a feature called "index merge", which can merge two separate indexes to fulfill the needs of one query. EXPLAIN will show if it is used. If it is not, one must add a combined index.
    When two indexes can be merged depends heavily on the used MySQL version. Whereas 5.1 was not that good and ignored some (a lot?) cases, the feature is more common to be used in e.g. version 5.6.
  2. This depends on the order of the columns inside the key. You can create (x,y) as a key and (y,x). A rear part of a combined index can only be used, if the parts before it were used. For SELECT...WHERE x=1 the index (x,y) can be used, whereas the index (y,x) cannot.

Upvotes: 2

Related Questions