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?
> select
> cast( (xStart+xEnd)/2/(select max(xEnd) from db)*64 as
> integer) cellX,
> cast( (yStart+yEnd)/2/(select max(yEnd) from db)*64 as
> integer) cellY,
> max(score)
> from db
> group by cellX, cellY
>
> Igor Tandetnik
>
> > query = prepare("
> > SELECT score FROM db WHERE
> > (xStart+xEnd)/2 >= :left AND
> > (xStart+xEnd)/2 < :right AND
> > (yStart+yEnd)/2 >= :top AND
> > (yStart+yEnd)/2 < :bottom
> > ORDER BY score DESC LIMIT 1
> > ")
> >
> > xJump = max(xEnd in db) / 64.0;
> > yJump = max(yEnd in db) / 64.0;
> > for(int x = 0; x < 64; x++){
> > for(int y = 0; y < 64; y++){
> > left = x*xJump, righ = (x+1)*xJump
> > top = y*yJump, bottom = (y+1)*yJump
> > bind(query, left, right, top, bottom)
> > ret = step(query)
> > output[x][y] = score or 0 if no ret
> > }
> > }
