List:General Discussion« Previous MessageNext Message »
From:Janusz Krzysztofik Date:November 24 2003 3:47pm
Subject:Inefficient use of index (was: Big difference in MyISAM and InnoDB SELECT speed)
View as plain text  
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