db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Stan <stangrad-de...@stanfordalumni.org>
Subject Does Derby optimize queries w/ ORDER BY and "LIMIT"? (w/ indexes?)
Date Tue, 05 Jul 2005 17:16:10 GMT
Does Derby optimize queries that use "ORDER BY" and "setMaxRows()" when
indexes are present?

My situation: I have an existing Derby table listing 2 million cities,
with the name, latitude, longitude, and population of each city. I want to
efficiently find the 50 most populous cities between (for example)
latitudes 35.2 and 41.7 and longitudes 19.8 and 27.9. The query is simple:

Statement s =
DriverManager.getConnection("jdbc:derby:test;create=false").createStatement();
            
s.setMaxRows(50);
            
rs = s.executeQuery("SELECT * FROM cities where lat>35.2 and lat<41.7 and 
lon>19.8 and lon<27.9 ORDER BY population desc");

There are indexes on lat, lon, and population, but the query seems to take
a long time. In particular, setting "s.setMaxRows(50)" doesn't seem to
speed things up at all. It looks like Derby finds ALL the cities in the
specified latitude/longitude range (over 50,000 of them), and that
setMaxRows() just limits how many rows it shows me, not how many rows it
computes.

I know that MySQL optimizes queries with LIMITs -- does Derby do the same?

At the risk of offending, is there a better way to do what I want (by
creating my own data structures for example) without necessarily using
Derby?

(In case anyone's interested, I'm trying to update a map, so finding the
biggest cities in a given area quickly is important)


Mime
View raw message