For an intra-site search I have this table in an SQLite3 database, that relates
ids to search terms with a certain weight:
CREATE TABLE search_index ( id INTEGER(7) REFERENCES other_table, term TEXT, weight INTEGER(2) DEFAULT 1 );
weight changes with importance of the
term. Think header text vs body copy. Typical entries might look like
> SELECT * FROM search_index; id|term|weight 1 |foo |10 1 |bar |5 2 |foo |4 3 |baz |20
I query the table by searching for the matching
term, then summing up the
SELECT id FROM search_index WHERE term = 'foo' OR term = 'bar' GROUP BY id ORDER BY SUM(weight) DESC LIMIT 0,128;
The results are paginated. To get the total matches, I count distinct matches:
SELECT COUNT(DISTINCT id) AS count FROM search_index WHERE term = 'foo' OR term = 'bar';
In theory, these two queries work like a charm and do exactly, what they should. However, in cases with huge result sets (up to 100,000 in my case) the queries become slow beyond usability.
I am quite sure, that the
ORDER BY SUM() clauses kill any possible query optimizations. I added several indexes, but none are used in the queries (which doesn't surprise me, to be honest), e.g. in an approach to get this optimization. However, I have no idea, if I can
- enhance the queries, so that they run with better performance, or
- change the schema somehow.
I am happy for any pointers or help. Changing the schema would be possible, so if I went the wrong direction with my
search_index table, I'd be glad to change that to a better design.
(Last point: My data is mostly static, there are close to zero write accesses to the table, if that's somehow helpful.)