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 admin@stripped
