Query for existence of objects across two multi-to-one relationships

querysubquery

I have three relations. The first is "MyObject", which contains an integer "value" attribute as well as a foreign key to "Config". The relationship is "multiple MyObjects to one Config".

The "Config" relation contains basically just a name. Finally, there is the "ConfigKV" relation, having three attributes: a key, a value, and a foreign key to "Config". Each Config has multiple ConfigKVs.

This is the SQL statement to create the database:

CREATE TABLE "MyObject" (
  "value" INTEGER NOT NULL,
  "cfg" INTEGER NULL,
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  CONSTRAINT "cfg_fk"
    FOREIGN KEY ("cfg")
    REFERENCES "Config" ("id"));

CREATE TABLE "Config" (
  "name" TEXT NOT NULL,
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT);

CREATE TABLE "ConfigKV" (
  "key" TEXT NOT NULL,
  "value" TEXT NOT NULL,
  "cfg" INTEGER NULL,
  "id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
  CONSTRAINT "cfg_fk"
    FOREIGN KEY ("cfg")
    REFERENCES "Config" ("id"));

I want to query for all MyObjects that:

has value 42
the associated Config of which has exactly two ConfigKVs associated:
- "Foo"->"Bar"
- "Fuz"->"Baz"

Aside from the "exactly" part above, I can achieve that with this query:

SELECT * FROM MyObject
INNER JOIN (
  SELECT * from ConfigKV WHERE key == 'foo' AND value == 'bar'
) as SQ1 on SQ1.cfg == MyObject.cfg
INNER JOIN (
  SELECT * from ConfigKV WHERE key == 'fuz' AND value == 'baz'
) as SQ2 on SQ2.cfg == MyObject.cfg
WHERE MyObject.value == 42;

The inner joins neatly eliminate all MyObjects that do not have the required Key-Value mappings associated. However, I have no clue how to exclude MyObjects the config of which has a superset of the specified Key-Value mappings. Any idea here?

Thanks a lot!

Best Answer

You can add this restriction with either a NOT EXISTS subquery or with LEFT JOIN / IS NULL:

SELECT * FROM MyObject
INNER JOIN (
  SELECT * from ConfigKV WHERE key = 'foo' AND value = 'bar'
) as SQ1 on SQ1.cfg = MyObject.cfg
INNER JOIN (
  SELECT * from ConfigKV WHERE key = 'fuz' AND value = 'baz'
) as SQ2 on SQ2.cfg = MyObject.cfg
WHERE MyObject.value = 42
  AND NOT EXISTS
      ( SELECT 1
        FROM ConfigKV
        WHERE ConfigKV.cfg = MyObject.cfg
          AND NOT ( key = 'foo' AND value = 'bar'
                 OR key = 'fuz' AND value = 'baz'
                  )
      ) ;

Related Solutions

Mysql – Unexplained InnoDB timeouts

I know this is really late, but you really need to capture the output of SHOW ENGINE INNODB STATUS; during that query to see why it's waiting.

If it happens a lot during a specific time, it would be easy to just grab that output every x seconds and hope you capture it (or perhaps artificially generate the load).

Mysql – Query performance with subquery and IN clause

Refactor the query as follows:

SELECT
    readings.*
FROM
    (
        SELECT boxsn FROM readings
        WHERE (time >= 1325404800) 
        AND (time < 1326317400) 
        ORDER BY `time` ASC
    ) readings_keys
    LEFT JOIN
    (
        SELECT id AS boxsn FROM boards WHERE siteId = '1'
    ) boards
    USING (boxsn)
    LEFT JOIN readings
    USING (boxsn)
;

Make sure you have the following indexes:

ALTER TABLE boards ADD INDEX siteId_id_ndx (siteId,id);
ALTER TABLE readings ADD INDEX time_boxsn_ndx (time,boxsn);

You can drop the other index

ALTER TABLE readings DROP INDEX boxsn_time_ndx;

You should definitely see a dramatic improvement in performance as the tables grow.

In your case,

The first EXPLAIN plan says you have to perform a lookup of SerialNumber for each row in readings against a list of value in memory
The second EXPLAIN plan says you have to perform a lookup of SerialNumber for each row in readings against a table.

UPDATE 2012-01-12 14:03 EDT

I refactored it again to make sure the readings keys and boards keys are combined correctly before retrieving the data from the readings table:

SELECT 
    readings.* 
FROM 
    ( 
        SELECT A.* FROM
        (
            SELECT boxsn FROM readings 
            WHERE (time >= 1325404800)  
            AND (time < 1326317400)  
            ORDER BY `time` ASC
        ) A
        LEFT JOIN
        (
            SELECT id AS boxsn
            FROM boards
            WHERE siteId = '1'
        ) B
        USING (boxsn)
        WHERE B.boxsn IS NOT NULL
    ) readings_keys 
    LEFT JOIN readings 
    USING (boxsn) 
;

Best Answer

Related Solutions

Mysql – Unexplained InnoDB timeouts

Mysql – Query performance with subquery and IN clause

UPDATE 2012-01-12 14:03 EDT

Related Question