List:General Discussion« Previous MessageNext Message »
From:gerald_clark Date:November 24 2003 3:53pm
Subject:Re: Inefficient use of index (was: Big difference in MyISAM and InnoDB SELECT speed)
View as plain text  
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=1
>>
>>--
>>MySQL General Mailing List
>>For list archives: http://lists.mysql.com/mysql
>>To unsubscribe:    http://lists.mysql.com/mysql?unsub=1
>>    
>>
>
>  
>


Thread
Big difference in MyISAM and InnoDB SELECT speedJanusz Krzysztofik24 Nov
RE: Big difference in MyISAM and InnoDB SELECT speedMechain Marc24 Nov
  • Inefficient use of index (was: Big difference in MyISAM and InnoDB SELECT speed)Janusz Krzysztofik24 Nov
  • Re: Inefficient use of index (was: Big difference in MyISAM and InnoDB SELECT speed)gerald_clark24 Nov
  • Re: Inefficient use of index (was: Big difference in MyISAM and InnoDB SELECT speed)Martijn Tonies24 Nov
  • Re: Inefficient use of index (was: Big difference in MyISAM and InnoDB SELECT speed)Janusz Krzysztofik24 Nov
  • Re: Inefficient use of index (was: Big difference in MyISAM and InnoDB SELECT speed)Martijn Tonies24 Nov
  • Re: Inefficient use of index (was: Big difference in MyISAM and InnoDB SELECT speed)Janusz Krzysztofik27 Nov
RE: Inefficient use of index (was: Big difference in MyISAM and InnoDB SELECT speed)Mechain Marc24 Nov