Hi!
>>>>> "Myk" == Myk Melez <[EMAIL PROTECTED]> writes:
Myk> Michael Widenius wrote:
>> You can check if the index are ok with CHECK TABLE; If this says the
>> index is ok, they are not corrupted.
>>
Myk> Apparently this doesn't work in all cases. "CHECK TABLE" told me
Myk> everything was fine, but dumping the data to a dump file via mysqldump
Myk> and then re-importing it into a new database fixed the problem, so there
Myk> must have been some undetected corruption in the database.
The only thing that a dump is that he index file gets distributed in a
different manner. This make affect the range optimizer in some range
cases.
Any chance you could give us access to the original 'not working' table
so that we can check this? CHECK TABLE should be able to find all
possible problems with the table; If not, I would like to know why it
failed.
>> The above shows that MySQL could use the index but decides to not use
>> this because it concludes that a table scan will be faster.
>>
>> To verify this can you send us the result for the following queries:
>>
>> SELECT count(*) FROM bugs WHERE bug_status IN ('NEW', 'ASSIGNED', 'REOPENED');
>> SELECT count(*) FROM bugs;
Myk> The results were about 25,000 and 131,000, respectively.
In a case like this (where so many rows are covered by the index) it's
not self evident that a table scan is not faster than an index scan.
Especially with many users using the key cache at the same time, a
table scan is likely to use less resources from the system.
What was the time difference in executing the query when MySQL used
the index and when it did the table scan ?
Regards,
Monty
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
