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