freelancing-god/thinking-sphinx — Issue #334: Querying all rows where delta = true
Author: morganick
Created: 2012-02-02 19:51:53+00:00
State: open

This query here does a full table scan:

SELECT IFNULL(MIN(`id`), 1), IFNULL(MAX(`id`), 1) FROM `table` WHERE `table`.`delta` = 1

Once this table gets big enough, it is no longer performant. Are you hoping that real time indexing happens soon so that we no longer have to think about deltas?

We use resque_delta and can now update delta indexes in parallel; running worker per index. We can easily drown a db box with doing these queries. (Uncached full table scans)

I was even toying with the idea of storing all [index_name, primary_key] of the records in Redis that needed to be updated instead of using the delta flag. This would eliminate that query all together, correct? I would also love to supply those ids to the main index query that grabs all the data instead scanning every record between the min and max. (Uncached can bound by min and max)

Would just like to hear your thoughts on this and let me know what I'm missing.

Thanks for all your hard work.