Hi joerg,
that was a excellent explanation.
Regards,
Pradeep Chandru.
Joerg Bruehe wrote:
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
********** DISCLAIMER **********
Information contained and transmitted by this E-MAIL is proprietary to
Sify Limited and is intended for use only by the individual or entity to
which it is addressed, and may contain information that is privileged,
confidential or exempt from disclosure under applicable law. If this is a
forwarded message, the content of this E-MAIL may not have been sent with
the authority of the Company. If you are not the intended recipient, an
agent of the intended recipient or a person responsible for delivering the
information to the named recipient, you are notified that any use,
distribution, transmission, printing, copying or dissemination of this
information in any way or in any manner is strictly prohibited. If you have
received this communication in error, please delete this mail & notify us
immediately at [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
