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

The weight changes with importance of the term. Think header text vs body copy. Typical entries might look like

> SELECT * FROM search_index;
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 weights:

    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:

    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 DISTINCT and GROUP BY/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

  1. enhance the queries, so that they run with better performance, or
  2. 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.)

  • SQLite does not support indexes on function results. You'd have to precompute everything. – CL. May 6 '15 at 8:34
  • Thanks, so there is little chance of speeding this up? Do you have any idea, how a schema for such a task could look like to give better performance? – Boldewyn May 6 '15 at 10:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.