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 > > } > > }