SQL index problem

[jy2003]

INDEX index_A (coll_a, coll_b),
INDEX index_B (col_b, coll_a),

Are these two indexes redundant?
The two indexes above actually contain the same contents in different order.
I can change a query from
"SELECT * FROM table WHERE coll_b=b AND coll_a=a"
to
"SELECT * FROM table WHERE coll_a=a AND coll_b=b",
so that I can make use of index_A, and get rid of index_B.

[Siemel Naran]

"jy2003" <jy****@sbcglobal.net> wrote in message news:YuTuc.62858

INDEX index_A (coll_a, coll_b),
INDEX index_B (col_b, coll_a),

Are these two indexes redundant?
No. First is useful if you want to sort by coll_a, and second is useful if
you want to sort by coll_b. But in your example below you fix coll_a and
coll_b to fixed values, so in that case the index is redundant.
The two indexes above actually contain the same contents in different order. I can change a query from
"SELECT * FROM table WHERE coll_b=b AND coll_a=a"
to
"SELECT * FROM table WHERE coll_a=a AND coll_b=b",
so that I can make use of index_A, and get rid of index_B.

You can drop one of the indexes and not bother rewriting your query. The
database engine is smart enough to realize to rearrange the where clause in
order to use the right index.

Now if you have lots of tables and a complicated where clause then the
database engine could get confused and may pick the wrong index. So after
careful analysis that tells you which is the correct index to use, use an
order by clause. Say we had only the index INDEX index_A (coll_a, coll_b).
Then the select statement would be

-- use only if explain shows database using the wrong index
-- and you know what the right index is
SELECT * FROM table WHERE coll_a=a AND coll_b=b
ORDER BY coll_a, coll_b;

MySql also has extensions use index, ignore index, and force index. See the
documentation for details, and which extensions work in which versions of
MySql (4.1 supports all of them). See my reply to the thread "Indexes with
OR queries" for an example.