Re: Doubt on Index Merge??
Ratheesh K J wrote: 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? You can test those scenarios yourself. You can use "ignore index" like this: select blah from table IGNORE INDEX (index_name) WHERE ; so that will tell mysql specifically not to use that index without you having to drop it and recreate it. Comes in handy ;) And it *always* depends on the situation (queries you run a lot vs queries that run once a month) and data you have. Asking us not to take that into account is silly. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Doubt on Index Merge??
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
