SQLite self-join performance
- by Derk
What I essentially want, is to retreive all features and values of products which have a particular feature and value.
For example: I want to know all available hard drive sizes of products that have an Intel processor.
I have three tables:
product_to_value (product_id, feature_id, value_id)
features (id, value) // for example Processor family, Storage size, etc.
values (id, value) // for example Intel, 60GB, etc
The simplified query I have now:
SELECT
features.name,
featurevalues.name,
featurevalues.value
FROM
products,
products as prod2,
features,
features as feat2,
values,
values as val2
WHERE
products.feature = features.id
AND
products.value = values.id
AND
products.product = prod2.product
AND
prod2.feature_id = feat2.id
AND
prod2.value_id = val2.id
AND
features.id = ?
AND
feat2.id = ?
All columns have an index. I am using SQLite.
The problem is that it's very slow (70ms per query, without the self-join it's <1ms). Is there a smarter way to fetch data like this? Or is this too much to ask from SQLite?
I personally think I am simply overlooking something, as I am quite new to SQLite.
