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]
