Hello all, I have a doubt on Index Merge.
We are currently using MySQL server 4.1.11. As a part of DB management I am currently doing an analysis on all the Tables. While looking into the table structures, their columns and Indexes I found that most of the tables have an Index on fields that have a very low cardinality. For an estimate let me say that there were indexes on fields with cardinality 17 for a table with 13 lac rows. So i decided to remove the Index on such fields. I made this decision because I assume that the probability of MySQL optimizer choosing such indexes is very low. MySQL would always choose a better index than this. Now i doubt my assumption when I move to MySQL server 5.0.X. In MySQL 5 there is a concept of Index Merge. So was it right for me to remove these indexes if we were to use MySQL 5? How much of a difference in terms of performance would removal of Index make in MySQL 4.1.11? How much of a difference in terms of performance would retaining of Index make in MySQL 5? Expecting a specific answer than a "depends on situation" kind of an answer... Thanks, Ratheesh Bhat K J