Janusz, > Now the question is: how should I set up (and maintain?) my MySQL server > to prevent it > from using indexes inefficiently?
Quite a good question, no idea. But if the query select url_id from urlword where deleted=?? and status=??? and origin=?; is a query that you will use very often, I still think that creating an index on (origin,status,deleted) is the good answer. Marc. -----Message d'origine----- De : Janusz Krzysztofik [mailto:[EMAIL PROTECTED] Envoyé : lundi 24 novembre 2003 16:48 A : Mechain Marc Cc : [EMAIL PROTECTED] Objet : Inefficient use of index (was: Big difference in MyISAM and InnoDB SELECT speed) 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:[EMAIL PROTECTED] > Envoyé : lundi 24 novembre 2003 13:58 > A : [EMAIL PROTECTED] > 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/[EMAIL PROTECTED] > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
