Ok, I tested this. The queries from best to worst were:
Query 1: Joins (0.016s; basically instant)
SELECT p.id, name
FROM parent p
JOIN relationship r1 ON p.id = r1.parent_id AND r1.other_id = 100
JOIN relationship r2 ON p.id = r2.parent_id AND r2.other_id = 101
JOIN relationship r3 ON p.id = r3.parent_id AND r3.other_id = 102
JOIN relationship r4 ON p.id = r4.parent_id AND r4.other_id = 103
Query 2: EXISTS (0.625s)
SELECT id, name
FROM parent p
WHERE EXISTS (SELECT 1 FROM relationship WHERE parent_id = p.id AND other_id = 100)
AND EXISTS (SELECT 1 FROM relationship WHERE parent_id = p.id AND other_id = 101)
AND EXISTS (SELECT 1 FROM relationship WHERE parent_id = p.id AND other_id = 102)
AND EXISTS (SELECT 1 FROM relationship WHERE parent_id = p.id AND oth
Query 3: Aggregate (1.016s)
SELECT p.id, p.name
FROM parent p
WHERE (SELECT COUNT(*) FROM relationship WHERE parent_id = p.id AND other_id IN (100,101,102,103))
Query 4: UNION Aggregate (2.39s)
SELECT id, name FROM (
SELECT p1.id, p1.name
FROM parent AS p1 LEFT JOIN relationship as r1 ON(r1.parent_id=p1.id)
WHERE r1.other_id = 100
UNION ALL
SELECT p2.id, p2.name
FROM parent AS p2 LEFT JOIN relationship as r2 ON(r2.parent_id=p2.id)
WHERE r2.other_id = 101
UNION ALL
SELECT p3.id, p3.name
FROM parent AS p3 LEFT JOIN relationship as r3 ON(r3.parent_id=p3.id)
WHERE r3.other_id = 102
UNION ALL
SELECT p4.id, p4.name
FROM parent AS p4 LEFT JOIN relationship as r4 ON(r4.parent_id=p4.id)
WHERE r4.other_id = 103
) a
GROUP BY id, name
HAVING count(*) = 4
Actually the above was producing the wrong data so it's either wrong or I did something wrong with it. Whatever the case, the above is just a bad idea.
If that's not fast then you need to look at the explain plan for the query. You're probably just lacking appropriate indices. Try it with:
CREATE INDEX ON relationship (parent_id, other_id)
Before you go down the route of aggregation (SELECT COUNT(*) FROM ...) you should read SQL Statement - “Join” Vs “Group By and Having”.
Note: The above timings are based on:
CREATE TABLE parent (
id INT PRIMARY KEY,
name VARCHAR(50)
);
CREATE TABLE other (
id INT PRIMARY KEY,
name VARCHAR(50)
);
CREATE TABLE relationship (
id INT PRIMARY KEY,
parent_id INT,
other_id INT
);
CREATE INDEX idx1 ON relationship (parent_id, other_id);
CREATE INDEX idx2 ON relationship (other_id, parent_id);
and nearly 800,000 records created with:
<?php
ini_set('max_execution_time', 600);
$start = microtime(true);
echo "<pre>\n";
mysql_connect('localhost', 'scratch', 'scratch');
if (mysql_error()) {
echo "Connect error: " . mysql_error() . "\n";
}
mysql_select_db('scratch');
if (mysql_error()) {
echo "Selct DB error: " . mysql_error() . "\n";
}
define('PARENTS', 100000);
define('CHILDREN', 100000);
define('MAX_CHILDREN', 10);
define('SCATTER', 10);
$rel = 0;
for ($i=1; $i<=PARENTS; $i++) {
query("INSERT INTO parent VALUES ($i, 'Parent $i')");
$potential = range(max(1, $i - SCATTER), min(CHILDREN, $i + SCATTER));
$elements = sizeof($potential);
$other = rand(1, min(MAX_CHILDREN, $elements - 4));
$j = 0;
while ($j < $other) {
$index = rand(0, $elements - 1);
if (isset($potential[$index])) {
$c = $potential[$index];
$rel++;
query("INSERT INTO relationship VALUES ($rel, $i, $c)");
unset($potential[$index]);
$j++;
}
}
}
for ($i=1; $i<=CHILDREN; $i++) {
query("INSERT INTO other VALUES ($i, 'Other $i')");
}
$count = PARENTS + CHILDREN + $rel;
$stop = microtime(true);
$duration = $stop - $start;
$insert = $duration / $count;
echo "$count records added.\n";
echo "Program ran for $duration seconds.\n";
echo "Insert time $insert seconds.\n";
echo "</pre>\n";
function query($str) {
mysql_query($str);
if (mysql_error()) {
echo "$str: " . mysql_error() . "\n";
}
}
?>
So once again joins carry the day.