Hi !
Parikh, Dilip Kumar schrieb: > > Hi , > > So you are trying to say that 1) when the Table has Low Cardinality, Mysql > wont use Index? Is this the logic behind your words? Extreme example: If you are manually looking up one entry from a list of five (say, in a cookbook), would you go through the index or just scan sequentially ? > > And also do you mean that the select query "without" index will be faster > than that of the select query "with" Index? I just don't believe it. Then > what is the purpose of Index?????????? Please clarify... Another extreme example: If you were to find a list all male soldiers in a "typical" army, would you go through an index on sex or just scan the payroll list, skipping the female ones ? For both cases, the logic is: Going through an index causes some overhead over a sequential scan (access the index, for each match follow the pointer to the "real" data) which you want (the system) to take only if that overhead is less than the overhead of scanning the base data and skipping the non-matches. Typically, both the index and the base data might be arranged sequentially, so scanning to the next entry is cheap, but following a reference from the index to some base record is a random access which is costly. So using the index is efficient only if the cost of (find matches in index) + ((hit rate) * (random data access)) is less than that of (sequential data scan). I have seen a 15 % hit rate used as a rule of thumb: If that optimizer expected a hit rate of more than 15 % (better: a selectivity worse than 15 %), it did not use the index at all but scan the base table. The reasoning was that sequentially scanning 6 - 7 entries (possibly using some read-ahead, disk caches etc) costs less than accessing one data record randomly. Regards, Jörg -- Joerg Bruehe, MySQL Build Team, [EMAIL PROTECTED] Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
