469,313 Members | 2,633 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,313 developers. It's quick & easy.

SQL index problem

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.
Jul 20 '05 #1
1 1099
"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.
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

reply views Thread by Mike Coppinger | last post: by
1 post views Thread by J. C. Clay | last post: by
reply views Thread by Guy Deprez | last post: by
3 posts views Thread by charley | last post: by
14 posts views Thread by Sean C. | last post: by
24 posts views Thread by Henrik Steffen | last post: by
85 posts views Thread by Russ | last post: by
18 posts views Thread by Dave | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.