| Problem with INNODB -> BETWEEN AND + ORDER BY query [message #3749] |
Fri, 28 November 2008 05:47  |
 Juergen
Messages: 1
Registered: November 2008 |
Junior Member |
|
|
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 table
WHERE artnr BETWEEN '120000' AND '130000'
AND menge IS NOT NULL
ORDER 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
|
|
|
| Re: Problem with INNODB -> BETWEEN AND + ORDER BY query [message #4139 is a reply to message #3749 ] |
Sat, 21 March 2009 21:14  |
MarkRose
Messages: 94
Registered: January 2008 |
Member |
|
|
Try
SELECT * FROM table
WHERE artnr > '120000' AND artnr < '130000'
AND menge IS NOT NULL
ORDER BY art ASC, p DESC
?
|
|
|
Members
Search
Help
Register
Login
Home
