Hi!
>>>>> "Myk" == Myk Melez <[EMAIL PROTECTED]> writes:
Myk> Steven Roussey wrote:
>> Are the tables defined the same on both servers?
>>
Myk> Yes, the tables are defined exactly the same.
>> Is the data the same?
>>
Myk> Just about. The data on the working server is a copy of the database on
Myk> the broken server. For previous tests I used a several-weeks-old copy
Myk> of the data, but for today's tests I re-copied the data over, so it
Myk> represents almost the same exact data set.
>> Is the query the same? Are both analyzed?
>>
Myk> Yes, I analyzed the tables on both databases before running the explain
Myk> queries, and the explain queries are exactly the same. The only
Myk> difference I can come up with is that the database on the working server
Myk> was dumped from the broken server via mysqldump and then imported into
Myk> the working server, while the database on the broken server was created
Myk> via table creation statements years ago and has since been updated from
Myk> the ISAM to the MyISAM table type and the database server from version
Myk> 3.22.30 to 3.23.44.
Myk> Is it possible that indexes were somehow corrupted in the process and
Myk> need to be rebuilt from scratch? Does it make sense to dump the data on
Myk> the broken server and re-import it into a different database on that server?
You can check if the index are ok with CHECK TABLE; If this says the
index is ok, they are not corrupted.
You can optimize the index layout by using OPTIMIZE TABLE.
But back to the original problem:
mysql> EXPLAIN SELECT bug_id FROM bugs WHERE bug_status IN ('NEW',
'ASSIGNED', 'REOPENED');
+-------+------+---------------+------+---------+------+--------+------------+
| table | type | possible_keys | key | key_len | ref | rows |
Extra |
+-------+------+---------------+------+---------+------+--------+------------+
| bugs | ALL | bug_status | NULL | NULL | NULL | 139425 | where
used |
+-------+------+---------------+------+---------+------+--------+------------+
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;
If the first query returns more than 30 % of the second, then MySQL
works as expected in this case.
Regards,
Monty
--
For technical support contracts, goto https://order.mysql.com/
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Michael Widenius <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO
/_/ /_/\_, /___/\___\_\___/ Helsinki, Finland
<___/ www.mysql.com
---------------------------------------------------------------------
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
