List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:May 15 2002 6:23pm
Subject:Re: MySQL 3.23.44 not using indexes
View as plain text  
Hi!

>>>>> "Myk" == Myk Melez <myk@stripped> 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 <monty@stripped>
 / /|_/ / // /\ \/ /_/ / /__   MySQL AB, CTO
/_/  /_/\_, /___/\___\_\___/   Helsinki, Finland
       <___/   www.mysql.com
Thread
MySQL 3.23.44 not using indexesMyk Melez22 Apr
  • Re: MySQL 3.23.44 not using indexesMyk Melez22 Apr
  • Re: MySQL 3.23.44 not using indexesMyk Melez24 Apr
Re: MySQL 3.23.44 not using indexesSteven Roussey23 Apr
  • Re: MySQL 3.23.44 not using indexesJohn Birrell23 Apr
  • Re: MySQL 3.23.44 not using indexesMyk Melez24 Apr
    • RE: MySQL 3.23.44 not using indexesSteven Roussey24 Apr
      • Re: MySQL 3.23.44 not using indexesMyk Melez24 Apr
        • Re: MySQL 3.23.44 not using indexesMichael Widenius15 May
      • Re: MySQL 3.23.44 not using indexesMyk Melez16 May
        • Re: MySQL 3.23.44 not using indexesMichael Widenius17 May
          • Re: MySQL 3.23.44 not using indexesJeremy Zawodny21 May
            • Re: MySQL 3.23.44 not using indexesMichael Widenius21 May
      • Re: MySQL 3.23.44 not using indexesLuciano Barcaro21 May