List:General Discussion« Previous MessageNext Message »
From:Mechain Marc Date:November 24 2003 1:08pm
Subject:RE: Big difference in MyISAM and InnoDB SELECT speed
View as plain text  
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

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