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
