(This is cross-posted from stackoverflow; I initially put it in the wrong place.)
So I have a database that's working a bit slower than I would expect and I'd like to describe what I'm doing so hopefully I can verify I'm not missing some performance tweak. Some details first:
I'm using java and the database is H2 embedded (although I'm seeing similar results regardless of database).
I've profiled / timed it and the problems are definitely in the database queries, specifically the executeQuery() call.
For this example the database table has 60K rows but my query involves a subset of 25K rows yet takes a full 1/2 second to return.
Here's how I've set up the database table:
CREATE TABLE rows ( id @AUTOTYPE@ PRIMARY KEY, x VARCHAR(255) NOT NULL, y VARCHAR(255) NOT NULL, z @TIMETYPE@ NOT NULL ) CREATE INDEX rows_index ON rows(x, y, z);
(Note that the parameterization is for auto increment and timestamp, respectively)
And my query looks like this:
SELECT * FROM rows WHERE x = ? AND y = ? ORDER BY z DESC
Now, I only care about the first row, but it needs to be the newest row, so in java it looks like this:
PreparedStatement stmt = conn.prepareStatement(query); stmt.setString(1, "foo"); stmt.setString(2, "bar"); stmt.setMaxRows(1);
Notice the last line has the setMaxRows() set to 1. To reiterate, the entire 'rows' table has about 60K rows and the query would return 25K rows if I wasn't setting it to return only 1.
So am I doing everything I can? Even though 1/2s (0.5s) is certainly not a long time it doesn't seem like 60K records should take that long, especially when it should be indexed. And I know time is relative, so this is just a modern workstation, not running on some sort of load-balanced server.
Thoughts? Thanks for taking a look.