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
>>
>>
>
>
>
