# Problem with INNODB -\> BETWEEN AND + ORDER BY query

**URL:** <https://forums.percona.com/t/problem-with-innodb-between-and-order-by-query/1013>\
**Category:** Other MySQL® Questions\
**Created:** [November 28, 2008, 5:17am UTC](https://forums.percona.com/t/problem-with-innodb-between-and-order-by-query/1013 "2008-11-28T05:17:22Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Juergen](https://avatars.discourse-cdn.com/v4/letter/j/65b543/32.png) [@Juergen](https://forums.percona.com/u/Juergen)\
**Post date:** [November 28, 2008, 5:17am UTC](https://forums.percona.com/t/problem-with-innodb-between-and-order-by-query/1013/1 "2008-11-28T05:17:22Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![MarkRose](https://avatars.discourse-cdn.com/v4/letter/m/85f322/32.png) [@MarkRose](https://forums.percona.com/u/MarkRose)\
**Post date:** [March 21, 2009, 7:44pm UTC](https://forums.percona.com/t/problem-with-innodb-between-and-order-by-query/1013/2 "2009-03-21T19:44:59Z")

</div>

Try

SELECT \* FROM tableWHERE artnr \> ‘120000’ AND artnr \< '130000’AND menge IS NOT NULLORDER BY art ASC, p DESC

?