db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mark Thornton <mthorn...@optrak.co.uk>
Subject Re: Index perfomance
Date Wed, 25 Mar 2009 21:04:23 GMT
Tim Dudgeon wrote:
> I found an interesting preformace problem that I'd welcome some help 
> in inderstanding.
> I have a table of 4,000,000 rows that has a DOUBLE column containing 
> floating point numbers.
> I run a query like this:
> select pk_column from the_table where the_double_column > 300 and 
> the_double_column < 320
> 271136 rows are returned.
> I then go through the ResultSet and extract all the id_column values.
> All of this is done using standard JDBC.
You are selecting about 7% of the rows. If the double column values are 
independent of the pk_column and a reasonable number of rows fit on a 
page then you are likely to have at least one desired row on most pages 
in the table. Without an index, the scan will proceed in the most 
convenient order through all the pages. With an index, it will still hit 
most of the pages, but in the most inconvenient order (plus of course 
having to read/decode the index pages).

Mark Thornton

View raw message