SQLite INTERSECT gives a huge performance decrease
- by Derk
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.
