From: gerald_clark
Date: November 24 2003 3:53pm
Subject: Re: Inefficient use of index (was: Big difference in MyISAM and InnoDB  SELECT speed)
List-Archive: http://lists.mysql.com/mysql/154391
Message-Id: <3FC22995.1000608@suppliersystems.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-2; format=flowed
Content-Transfer-Encoding: 8bit

You are not using any indicies, because there aren't any that could be 
used in this query.
Try adding an index on (status,deleted)

Janusz Krzysztofik wrote:

>Marc,
>
>Thank you for your prompt answer.
>
>I run EXPLAIN in both cases and got:
>
>MyISAM (fast):
>mysql> explain select url_id from urlword where deleted=0 and status=200
>and origin=1;
>+---------+------+---------------+------+---------+------+----------+------------+
>| table   | type | possible_keys | key  | key_len | ref  | rows     |
>Extra      |
>+---------+------+---------------+------+---------+------+----------+------------+
>| urlword | ALL  | crc           | NULL |    NULL | NULL | 46648925 |
>where used |
>+---------+------+---------------+------+---------+------+----------+------------+
>1 row in set (0.18 sec)
>
>InnoDB (very slow):
>mysql> explain select url_id from urlword where deleted=0 and status=200
>and origin=1;
>+---------+------+---------------+------+---------+-------+----------+------------+
>| table   | type | possible_keys | key  | key_len | ref   | rows     |
>Extra      |
>+---------+------+---------------+------+---------+-------+----------+------------+
>| urlword | ref  | crc           | crc  |       5 | const | 16951116 |
>where used |
>+---------+------+---------------+------+---------+-------+----------+------------+
>1 row in set (0.22 sec)
>
>
>So I retried InnoDB with "ignore index (crc)" and got:
>
>Sample disk usage (iostat):
>Device:    rrqm/s wrqm/s   r/s   w/s  rsec/s  wsec/s    rkB/s    wkB/s
>avgrq-sz avgqu-sz   await  svctm  %util
>/dev/scsi/host1/bus4/target0/lun0/disc
>           32586.70   0.00 530.10  0.00 66233.60    0.00 33116.80    
>0.00   124.95    15.35   28.99  15.11  80.10
>
>Processing time:
>Query OK, 14274315 rows affected (5 min 43.23 sec)
>
>That's it, 5 minutes, not 32 hours.
>
>Next I found that this issue is not related to InnoDB. On my second
>server with almost
>the same data in MyISAM tables the query also uses the index and lasts
>forever.
>
>Now the question is: how should I set up (and maintain?) my MySQL server
>to prevent it
>from using indexes inefficiently?
>
>Janusz
>
>--------------------
>Mechain Marc wrote:
>  
>
>>Innodb may not use an index (full scan) where MyIsam does (index crc which is the only relevant one).
>>
>>Have you done an EXPLAIN on your query ?
>>
>>May be an index on (origin,status,deleted) could help.
>>
>>Marc.
>>
>>-----Message d'origine-----
>>De : Janusz Krzysztofik [mailto:jkrzyszt@stripped]
>>Envoyé : lundi 24 novembre 2003 13:58
>>A : mysql@stripped
>>Objet : Big difference in MyISAM and InnoDB SELECT speed
>>
>>Hello,
>>
>>I am trying to optimize MySQL (3.23.49 from Debian stable) setup for
>>ASPseek application. I decided to try InnoDB in order to be able
>>to update tables while performing time consuming selects.
>>After converting all tables to InnoDB I noticed a big difference
>>in processing speed of one of the SELECT queries performed by the application.
>>
>>My configuration file /etc/my.cnf is based on my-huge.cnf example.
>>
>>Table structure:
>>create table urlword(url_id integer auto_increment primary key,
>>        site_id integer not null,
>>        tree_id integer not null,
>>        deleted tinyint DEFAULT 0 NOT NULL,
>>        url varchar(128) not null,
>>        next_index_time INT NOT NULL,
>>        status int(11) DEFAULT '0' NOT NULL,
>>        crc char(32) DEFAULT '' NOT NULL,
>>        last_modified varchar(32) DEFAULT '' NOT NULL,
>>        etag varchar(48) DEFAULT '' NOT NULL,
>>        last_index_time INT NOT NULL,
>>        referrer int(11) DEFAULT '0' NOT NULL,
>>        tag int(11) DEFAULT '0' NOT NULL,
>>        hops int(11) DEFAULT '0' NOT NULL,
>>        redir integer,
>>        origin integer,
>>        unique index(url),
>>        index(next_index_time),
>>        index(hops,next_index_time),
>>        index crc (origin, crc(8)));
>>
>>Query:
>>select url_id from urlword where deleted=0 and status=200 and origin=1
>>
>>MyISAM:
>>Table status:
>>| Name    | Type   | Row_format | Rows     | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Create_options     | Comment |
>>+---------+--------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+---------+
>>| urlword | MyISAM | Dynamic    | 46648925 |            143 |  6714978360 |   1099511627775 |   4052629504 |         0 |       46648929 | 2003-11-21 14:13:28 | 2003-11-22 04:01:16 | NULL       | max_rows=100000000 |         |
>>
>>Sample disk usage (iostat -x -d /dev/scsi/... 10):
>>Device:    rrqm/s wrqm/s   r/s   w/s  rsec/s  wsec/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
>>/dev/scsi/host1/bus4/target0/lun0/disc
>>           5510.20   0.20 361.10  0.40 46970.40    4.80 23485.20     2.40   129.95    16.41   45.39  27.11  98.00
>>
>>Processing time:
>>Query OK, 14274315 rows affected (4 min 54.88 sec)
>>
>>InnoDB:
>>Table status:
>>| Name    | Type   | Row_format | Rows     | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options     | Comment                 |
>>+---------+--------+------------+----------+----------------+-------------+-----------------+--------------+-----------+----------------+-------------+-------------+------------+--------------------+-------------------------+
>>| urlword | InnoDB | Dynamic    | 44477984 |            228 | 10150215680 |            NULL |  13322158080 |         0 |       46648929 | NULL        | NULL        | NULL       | max_rows=100000000 | InnoDB free: 6715392 kB |
>>
>>Sample disk usage (iostat -x -d /dev/scsi/... 10):
>>Device:    rrqm/s wrqm/s   r/s   w/s  rsec/s  wsec/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
>>/dev/scsi/host1/bus4/target0/lun0/disc
>>           2179.10   0.00 94.90  0.00 4548.00    0.00  2274.00     0.00    47.92     9.96  104.85 104.00  98.70
>>
>>Processing time:
>>Query OK, 14274315 rows affected (1 day 8 hours 46 min 46.70 sec)
>>
>>Could someone please explain me what can be the reason of this difference
>>and if there is a way to optimize InnoDB to perform better?
>>
>>Thanks,
>>Janusz
>>
>>--
>>MySQL General Mailing List
>>For list archives: http://lists.mysql.com/mysql
>>To unsubscribe:    http://lists.mysql.com/mysql?unsub=marc.mechain@stripped
>>
>>--
>>MySQL General Mailing List
>>For list archives: http://lists.mysql.com/mysql
>>To unsubscribe:    http://lists.mysql.com/mysql?unsub=jkrzyszt@stripped
>>    
>>
>
>  
>