List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:November 26 1999 2:04pm
Subject:Re: isamchk, keys and cardinality
View as plain text  
Hi!

>>>>> "sinisa" == sinisa  <sinisa@stripped> writes:

sinisa> Steve Ruby writes:
>> 
>> How important is the cardinality value in the show keys from database
>> 
>> I understand what it does basically, but there is nothing in the manual
>> that indicates if this distribution of keys is not up to date queries
>> could fail. Is is possible for queries to fail if the value of
>> cardinality
>> is NULL? (I was sure I had queries that failed when the cardinality was
>> not up to date, perhaps it was using the key and finding nothing in
>> there
>> but I cannot reproduce this now).
>> 
>> If cardinality remains null and isamchk -a is not run over time will any
>> other parts of the keys be affected (fail to update for example)
>> 
>> I assume I have to stop the server to run isamchk -a since it modifies
>> the
>> table itself?
>> 
>> Should I have to update the cardinality every time I:
>> drop or add a key
>> drop or add a column
>> 
>> 
>> If this happens every now and again and stopping the server is a bad
>> idea
>> in this case what is the alternative.
>> 
>> TIA
>> Steve
>> 
>> 
>> 

sinisa> Hi!
sinisa> The answer to your question is yes it is possible but not probable.

sinisa> Running isamchk --analyze for non-unique keys is recommendable, very
sinisa> much.

sinisa> If you do not run it, your queries will be sup-optimal. But as this
sinisa> affects only non-unique keys, it is not so bad.

sinisa> No, you do not have to run isamchk -a every time that you add key or
sinisa> remove non-key column. Just after many  inserts/updates/deletes
sinisa> happened ,  or load data.

Hi!

To clearify some things.

The cardinality is only used to find the 'optimal' key to use.  It
should never affect the result of the query, only the speed.

You can actually run [my]isamchk -a on a table that is used by MySQL
without having to take down the server if the server is not updating
the table.  The only 'problem' is that MySQL will not notice the
updated cardinality until it reopens the table (for example after one
have used 'flush tables')

Regards,
Monty

PS: Sorry for the late reply;  I am still trying to catch up with the
    mails that piled up during my vacation.
Thread
isamchk, keys and cardinalitySteve Ruby4 Nov
  • Re: isamchk, keys and cardinalitysinisa4 Nov
    • Re: isamchk, keys and cardinalityMichael Widenius26 Nov