I ended up running some tests using 5 million rows of products. I used
about 5 properties that a product should always be matched to, and then
I used the following in the select;
(CASE property1 in (option1, option2, option3, etc) WHEN TRUE THEN 1
ELSE 0 END)
+ (CASE property2 in (option1, option2, option3, etc) WHEN TRUE THEN 1
ELSE 0 END)
+ (CASE property3 in (option1, option2, option3, etc) WHEN TRUE THEN 1
ELSE 0 END)
...
AS numberOfMatchingProperties
That way I can use the number of matching properties in the order by
clause and have the properties that must always match filter out the
bulk of the 5 million records.
The tests that I've done return around 100.000 records in about 100 to
150 milliseconds using this technique, and using OFFSET and LIMIT to
paginate those by about 15 records each time is very very fast.
This I can live with :) Thx for letting me pick your brains on this a
little.
Cheers,
Ron
