Performance issue with WHERE and ORDER BY in HSQLDB

Question

I have a simple table called history_point which contains the following columns:

id - INTEGER PK
device_id - INTEGER
registered - TIMESTAMP
double_value - DOUBLE
channel - INTEGER
type - VARCHAR(100)
int_value - INTEGER

The table also have index defined for the following column combinations:

id
device_id
registered
channel
device_id, channel, registered

The table contains approximately 200000 rows. I run queries using the Java code below.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

public class JdbcMain {
    public static void main(String[] args) throws Exception {
        Class.forName("org.hsqldb.jdbcDriver");
        Connection con = DriverManager.getConnection("jdbc:hsqldb:file:db/homeki.db;ifexists=true");
        Statement stmt = con.createStatement();
        long start = System.currentTimeMillis();
        ResultSet rs = stmt.executeQuery("<SQL query goes here>");
        if (rs.next()) {
            System.out.println("Registered: " + rs.getDate("registered"));
        }
        long dur = System.currentTimeMillis() - start;
        System.out.println("Took " + dur + " ms.");
        stmt.execute("SHUTDOWN");
        con.close();
    }
}

When I run the query SELECT * FROM history_point WHERE device_id = 3 AND channel = 0 LIMIT 1, it takes ~5 ms. If I run the query SELECT * FROM history_point ORDER BY registered DESC LIMIT 1, it also takes ~5 ms. But, if I run the query SELECT * FROM history_point WHERE device_id = 3 AND channel = 0 ORDER BY registered DESC LIMIT 1, it takes ~1000ms!

I guess that could have been understandable, considering what they say at http://www.hsqldb.org/doc/1.8/guide/ch02.html#N1033B, "HSQLDB does not use indexes to improve sorting of query results" (which by the way sounds strange to me).

However, if I run the last query in Eclipse using the Eclipse Data Tools Platform's SQL Scrapbook, it executes in ~5 ms. Any query I throw at it using the SQL Scrapbook executes in ~5 ms. Why is that? They're both using the same JDBC driver and the same database.

There are ~25000 rows in history_point with device_id = 3 and channel = 0, if that matters.

The connection string I use is jdbc:hsqldb:file:db/mystorage.db. The table was created as a CACHED table (all data is stored on disk).

Can anyone explain this?

Thanks in advance!

Which version of HSQLDB? If you are using the latest version please refer to the online version 2.x Guide. — fredt
You are absolutely right. I use HSQLDB 2.2.8, and was looking at the Guide for 1.8. Here's the same section for HSQLDB 2.X: hsqldb.org/doc/2.0/guide/sqlgeneral-chapt.html#sgc_index_speed. Does not seem to have the same limitation (uses index for ORDER BY). The problem still remains though.. — joscarsson

fredt fredt · Accepted Answer · 2012-04-01T09:17:45

If the ORDER BY columns are all covered by an index, and the query condition can use a different index, then the index for the query condition is used. Currently, HSQLDB can use the index on (device_id, channel, registered) for the query condition, but not for sorting on the registered column at the same time.

In version 2.2.8, you can force the use of the index on the ORDER BY columns when there is a LIMIT clause. In this case, the index on registered is used:

SELECT * FROM history_point WHERE device_id = 3 AND channel = 0 
ORDER BY registered DESC LIMIT 1 USING INDEX

But perhaps this is the best way to use the index on the three columns:

SELECT * FROM history_point WHERE device_id = 3 AND channel = 0 
ORDER BY device_id DESC, channel DESC, registered DESC LIMIT 1 USING INDEX

Performance issue with WHERE and ORDER BY in HSQLDB

1 Answers