I have the following table:
create table x (
select * from y where t1 and not (t2 or t3 or t4 or t5);
create table pivot as (
max(if(property=1,true,false)) as 't1',
max(if(property=2,true,false)) as 't2',
max(if(property=3,true,false)) as 't3',
max(if(property=4,true,false)) as 't4',
max(if(property=5,true,false)) as 't5'
select * from pivot where t1 and not (t2 or t3 or t4 or t5);
I assume that
id is not unique and an existing record
(some_id, property_id) means that the property is
First, I would notice that
I need to efficiently run queries that test multiple property values for a given id and
I may want a query that gets all ids with a property satisfying the condition: 1 and not (2 or 3 or 4 or 5) may lead to completely different queries.
But here is my idea. Some more assumptions:
(id, property)is unique (an you even have the corresponding UNIQUE index)
Now, if you have an index on
(property, id), then the following query will take all matching
ids from the covering index (that is quickly):
SELECT id FROM t1 WHERE property = 150;
If this query leads to a significantly smaller result set than the whole table, you can afford to make another fast correlated subquery for another property that will significantly decrease the result set. This subquery will require another covering index
(id, property) and the corresponding UNIQUE index is what it needs:
SELECT id FROM t1 WHERE property = 150 AND NOT EXISTS ( SELECT 1 FROM t2 WHERE t2.id = t1.id AND t2.property = 130 ) AND NOT EXISTS ( SELECT 1 FROM t2 WHERE t2.id = t1.id AND t2.property = 90 );
If an earlier correlated subquery results to false, all the following subqueries will not be executed for the row. That is why the order is crucial.
You will need to play with the properties order, and probably hardcode that in the code that executes the query.
UPD: then, I afraid, you do not have much choice. The best you can do is to walk through the index in a single pass and compute what you need. The speed of the query then will mainly depend on the number of rows in your table. So, again assuming you have the UNIQUE index on (id, property), you can write something like:
SELECT id FROM t1 GROUP BY id HAVING COUNT(IF(property=150, 1, NULL)) AND NOT COUNT(IF(property=130, 1, NULL)) AND NOT COUNT(IF(property=90, 1, NULL));