Hi there,
I have a performance problem with inno.
I switched one table from MyISAM to INNODB because of transaction support:
the following query
SELECT * FROM tableWHERE artnr BETWEEN ‘120000’ AND '130000’AND menge IS NOT NULLORDER BY art ASC, p DESC
takes 0.8s to load with myisam, which is ok.
But with inno it takes 120+ sec (with heavy I/O activity).
The table contains only 500000 records. In both cases, no key is used.
But even if I add a key to artnr, the results are the same.
In my eyes this should not happen, even with no keys.
Another thing I don’t understand:
MyISAM uses the index, INNODB not:
±—±------------±------±-----±--------------±-----±--------±-----±-------±----------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |±—±------------±------±-----±--------------±-----±--------±-----±-------±----------------------------+| 1 | SIMPLE | table | ALL | TEST | NULL | NULL | NULL | 533968 | Using where; Using filesort |±—±------------±------±-----±--------------±-----±--------±-----±-------±----------------------------+
If I change ‘130000’ to something between ‘120000’ and ‘128982’ (or add a force index) the key is used with INNO but the performance is still very poor.
±—±------------±------±------±--------------±-----±--------±-----±------±----------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |±—±------------±------±------±--------------±-----±--------±-----±------±----------------------------+| 1 | SIMPLE | fpos | range | TEST | TEST | 48 | NULL | 86134 | Using where; Using filesort |±—±------------±------±------±--------------±-----±--------±-----±------±----------------------------+
Does anybody know, why INNODB is so slow with this query?
I mean, maybe I can add a lookup myisam table or add an efficient index, but I don’t understand what is happening in the background. I even could export all data manually to a csv file, sort the result with excel and reimport it to the database in less time (The “SELECT * FROM TABLE” statement takes 2 second to dump all data).
Oh, I forgot: The table:
CREATE TABLE tableX ( id int(10) unsigned NOT NULL auto_increment, art int(11) unsigned NOT NULL default ‘0’, p int(11) unsigned NOT NULL default ‘0’, artnr varchar(6) character set latin1 default NULL, name varchar(45) character set latin1 default NULL, …, PRIMARY KEY USING BTREE (id), KEY TEST USING BTREE (artnr)) ENGINE=InnoDB/MyISAM
Server = 5.1