Performance issue with WHERE and ORDER BY in HSQLDB
I have a simple table named history_point
that 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 provides an index for the following column combinations:
-
id
-
device_id
-
registered
-
channel
-
device_id
,channel
,registered
The table contains approximately 200,000 rows. I am running 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 request SELECT * FROM history_point WHERE device_id = 3 AND channel = 0 LIMIT 1
, it takes ~ 5ms. If I run a query SELECT * FROM history_point ORDER BY registered DESC LIMIT 1
, it also takes ~ 5ms. But , if I run the request SELECT * FROM history_point WHERE device_id = 3 AND channel = 0 ORDER BY registered DESC LIMIT 1
, it takes ~ 1000ms!
I think this might be understandable given what they say at http://www.hsqldb.org/doc/1.8/guide/ch02.html#N1033B , "HSQLDB does not use indexes to improve sorting query results" ( which, by the way, sounds strange to me).
However , if I run the last query in Eclipse using the SQL Scrapbook for the Eclipse Data Tools Framework, it runs in ~ 5ms. Any query I throw at it using the SQL Scrapbook is executed in ~ 5ms. Why is this? They both use the same JDBC driver and the same database.
There history_point
are ~ 25000 lines with device_id = 3
and channel = 0
if it matters.
The connection string to use jdbc:hsqldb:file:db/mystorage.db
. The table was created as a table CACHED
(all data is stored on disk).
Can anyone explain this?
Thanks in advance!
source to share
If the ORDER BY columns are 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 an index for a (device_id, channel, registered)
query condition, but not a sort on a column registered
at the same time.
As of 2.2.8, you can enforce an index on ORDER BY columns when there is a LIMIT clause. In this case, the index for registered ones 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 an index on 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
source to share