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
