Announcement

Announcement Module
Collapse
No announcement yet.

Problem with INNODB -> BETWEEN AND + ORDER BY query

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Problem with INNODB -> BETWEEN AND + ORDER BY query

    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

  • #2
    Try


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


    ?

    Comment

    Working...
    X