Brannon King <brannonking-/[EMAIL PROTECTED]>
wrote:
Thanks, Igor, you've inspired and saved me yet again. The subqueries
you had used for the x/yEnd did not work, but the rest did and I have
that maxim information beforehand anyway. Here's how it shook down:
select
cast(cast((xStart+xEnd) as double)/2/15518.5 as integer) cellX,
cast(cast((yStart+yEnd) as double)/2/15603.171875 as integer) cellY,
max(score)
from results_1
group by cellX, cellY;
where the two real numbers are the preknown maxims divided by 64.
That query takes 8 seconds on a table with 560k rows (on my AthlonXp
2200). That's certainly not bad speed. I wasn't sure what speed I
should expect. The query plan shows that neither the index on xEnd
nor the index on yEnd are used. Is there an index I could create that
this query could use?
No index would help here - the speed is gated by GROUP BY clause that
uses calculated values. That requires sorting on the fly. What might
help is precalculating cellX and cellY, actually storing them in the
table and building an index on them.
Also, if you are willing to write a little code, you can do all the work
in a single pass over the table without any sorting or conditions, which
would be as fast as it gets. Consider (pseudocode):
query =
select
cast(cast((xStart+xEnd) as double)/2/15518.5 as integer) cellX,
cast(cast((yStart+yEnd) as double)/2/15603.171875 as integer) cellY,
score
from results_1;
int output[64][64] = {0};
for each (record in query) {
if (output[record.cellX][record.cellY] < record.score)
output[record.cellX][record.cellY] = record.score;
}
Igor Tandetnik
