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.
精彩评论