开发者

h2database is very slow on order by

I have a java applet what has embeeded h2 database, and table with 200.000 rows.

Table:

    CREATE TABLE `DB`.`TEST` (
        `id` INT UNSIGNED PRIMARY KEY,
        `from` VARCHAR(80)  NOT NULL,
        `from_group` SMALLINT UNSIGNED,
        `to` VARCHAR(80)  NOT NULL,
        `to_group` SMALLINT UNSIGNED,
        `type` SMALLINT UNSIGNED NOT NULL,
        `class` SMALLINT UNSIGNED NOT NULL,
        `direction` TINYINT UNSIGNED NOT NULL,
        `start` TIMESTAMP  NOT NULL,
        `answer` TIMESTAMP ,
        `end` TIMESTAMP NOT NULL,
        `duration` INT UNSIGNED,
        `bill` INT UNSIGNED,
        `cost` FLOAT UNSIGNED
    )

When i do simple query SELECT * FROM DB.TEST ORDER BY id ASC LIMIT 50 - it's executes perfectly about 3-4 ms. But when a change order by to desc, situation is change and query takes about 8-10 seconds.

开发者_开发百科

Is anyway solution to make order by faster ?

Thanks


By doing an order by on a non-indexed column you are forcing your database to scan through each row to compare the values. Your best bet is to create an index on the row you want to sort on. http://www.h2database.com/html/performance.html#storage_and_indexes.
h2db is also by design slow on large amounts of data.
The syntax would be to add this after your CREATE TABLE DDL:

CREATE INDEX IDX_TO ON DB.TEST(TO);  

When adding indexes keep in mind that the database has more work to do on inserts, updates and deletes. So keep them to a minimum.

Note: Your original query of sort by id is doing a sort on the primary key of the database which is an indexed column and that is why it is faster.


H2 requires that you create a descending index in this case:

CREATE INDEX IDX_TO_DESC ON DB.TEST(TO DESC);  

That means, the index sorts this column in descending order.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜