Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

(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 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:


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");

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.

share|improve this question
Even if you fetch only 1 row, the database still has to do some extra work, because of the order by and the default ascending index order. While I am not familiar with H2, I think creating the index as (x, y, z desc) should help. – Balazs Papp Mar 20 '14 at 23:42
I googled and found that there is an EXPLAIN statement you can use to get the optimizer plan. compare the following queries SELECT * FROM rows WHERE x = ? AND y = ? ORDER BY z DESC and SELECT * FROM rows WHERE x = ? AND y = ? ORDER BY z The latter should use the index and be faster if the assumptions of @Balazs Papp are right. compare to SELECT * FROM rows WHERE x = ? AND y = ? without any sorting. – miracle173 Mar 21 '14 at 0:32
But I found… and I think it is possible that stmt.setMaxRows(1); does not limit the data retrieved by the database to one row.There is a LIMIT and a TOP clause to restrict the number of rows sent from the darabase. Check if this makes any difference. Maybe you can show us the explain output and the profiling results. – miracle173 Mar 21 '14 at 0:33
@miracle173: I did indeed try to use LIMIT and it didn't make a difference. I found the H2 docs and it said you can use them interchangeably (i.e., LIMIIT or setMaxRows()). Thanks for the information about EXPLAIN, I had never heard of it, and it is something I will look into. Thanks! – rjcarr Mar 21 '14 at 0:35
@BalazsPapp: I haven't tried an index like that, and didn't know it was possible, but I will give it a shot as I've now seen it in other places. Thanks! – rjcarr Mar 21 '14 at 0:36

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.