Search code examples
mysqlindexingcomposite-index

Do composite indexes in MySql work both ways round?

Consider a MySql table having the following schema

+-------------------+------------+------+-----+-
| Field             | Type       | Null | Key | 
+-------------------+------------+------+-----+-
| id                | int(11)    | NO   | PRI | 
| user_id           | int(11)    | YES  | MUL | 
| following_user_id | int(11)    | NO   | MUL | 
+-------------------+------------+------+-----+-

Now i need queries like

select * from table where user_id = <x> and following_user_id = <y>;

and also

select * from table where following_user_id = <x> and user_id = <y>;

So I am considering composite indexes on the 2 columns like so:

index(user_id, following_user_id)

AND

index(following_user_id, user_id)

1) The indexes are created as desired, but will they work when the records are many (~ Millions) ?

2) Will the indexes speed up the queries, using the right index at the right time?

PS: I don't need sort/range selection queries, only direct match queries. Is there any better indexing scheme available for this requirement?

Solution

  • Your queries are the same from the perspective of the compiler. Either index will work. The order of the clauses in the where statement is immaterial to qualifying a query for an index.

    However, if you have inequalities or only one clause, then the ordering in the index makes a difference.

    So, the index index(user_id, following_user_id) would be useful for these situations:

    • any comparison directly on user_id (except <>)
    • user_id = XXX and folowing_user_id = YYY
    • user_id = XXX and folowing_user_id /IN values

    It would not be used for:

    • folowing_user_id < YYY