SQLite INTERSECT gives a huge performance decrease

Posted by Derk on Stack Overflow See other posts from Stack Overflow or by Derk
Published on 2010-04-01T14:51:33Z Indexed on 2010/04/01 14:53 UTC
Read the original article Hit count: 609

Filed under:

Performance

I have a query that runs in less than 1 ms:

    SELECT
     product_to_value.category AS category,
     features.name AS featurename,
     featurevalues.name AS valuename

    FROM
     product_to_value,
     features,
     featurevalues

    WHERE
     product_to_value.category IN(:int, :bla, :bla1)
     AND    
     product_to_value.feature = features.int
     AND
     product_to_value.value = featurevalues.int
    LIMIT 10

However, when I combine it with another query using INTERSECT, the query now takes more than 250ms:

SELECT
 product_to_value.category AS category,
 features.name AS featurename,
 featurevalues.name AS valuename

FROM
 product_to_value,
 features,
 featurevalues

WHERE
 product_to_value.category IN(:int, :bla, :bla1)
  AND    
 product_to_value.feature = features.int
  AND
 product_to_value.value = featurevalues.int

INTERSECT

SELECT
 product_to_value.category AS category,
 features.name AS featurename,
 featurevalues.name AS valuename

FROM
 product_to_value,
 features,
 featurevalues

WHERE
 product_to_value.category IN(:int, :bla, :bla1)
  AND    
 product_to_value.feature = features.int
  AND
 product_to_value.value = featurevalues.int
LIMIT 10

This can't be right. I've tried several index combinations, for example an index on all columns I use in my query, but to no avail. I've tried compound indexes as well, but they only slow things down even more.

I have read a few things about SQLite and how it treats indexes.

I know SQLite is capable of delivering sick performance, and surely I must be overlooking something.

Developer IT