Как вы выполняете И с соединением?

Хорошо, я проверил это. Запросы от лучшего к худшему:

Запрос 1: соединения (0,016 с; в основном мгновенно)

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

Запрос 2: СУЩЕСТВУЕТ (0,625 с)

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

Запрос 3: Сводка (1,016 с)

SELECT p.id, p.name FROM parent p WHERE (SELECT COUNT(*) FROM отношения WHERE parent_id = p.id AND other_id IN (100,101,102,103))

Запрос 4: UNION Aggregate (2,39 с)

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

На самом деле вышеизложенное выдавало неверные данные, так что это либо неправильно, либо я сделал что-то не так. В любом случае, это просто плохая идея.

Если это не быстро, вам нужно посмотреть план объяснения для запроса. Вероятно, вам просто не хватает соответствующих индексов. Попробуйте с:

CREATE INDEX ON relationship (parent_id, other_id)

Прежде чем идти по маршруту агрегации (SELECT COUNT(*) FROM...), вы должны прочитать оператор SQL — «присоединиться» или «группировать по и по наличию».

Примечание. Вышеуказанные сроки основаны на:

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);

и почти 800 000 записей, созданных с помощью:

<?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";
    }
}
?>

Так что еще раз присоединяется нести день.

Учитывая, что родительская таблица содержит уникальный ключ (parent_id, other_id), вы можете сделать это:

select p.id, p.name 
  from parent as p 
 where (select count(*) 
        from relationship as r 
       where r.parent_id = p.id 
         and r.other_id in (1,2)
        ) >= 2

Немного упрощая, это должно работать, и эффективно.

SELECT DISTINCT p.id, p.name
FROM родительского p
INNER JOIN отношение r1 ON p.id = r1.parent_id AND r1.other_id = 1
INNER JOIN отношение r2 ON p.id = r2. parent_id И r2.other_id = 2

потребуется по крайней мере одна объединенная запись для каждого "другого" значения. И оптимизатор должен знать, что ему нужно найти только одно совпадение для каждой, и ему нужно только прочитать индекс, а не любую из вспомогательных таблиц, на одну из которых вообще нет ссылок.

Я на самом деле не проверял это, но что-то вроде строк:

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 = 1
  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 = 2
   -- etc
) GROUP BY id, name
HAVING count(*) = 2

Идея в том, что вам не нужно выполнять многосторонние соединения; просто объедините результаты обычных соединений, сгруппируйте по своим идентификаторам и выберите строки, которые появились в каждом сегменте.

Это распространенная проблема при поиске нескольких партнеров через соединение «многие ко многим». Это часто встречается в службах, использующих концепцию «тегов», например. Переполнение стека

См. мой другой пост о лучшей архитектуре для тега (в ваш случай "другое") хранилище

Поиск состоит из двух шагов:

1. Найдите все возможные кандидаты TagCollections, которые имеют любые/все необходимые вам теги (может быть проще, используя курсор конструкции цикла)
2. Выберите данные, соответствующие TagCollection

Производительность всегда выше из-за того, что коллекций тегов значительно меньше, чем элементов данных для поиска.

Вы можете сделать это с помощью вложенного выбора, я тестировал его в MSSQL 2005, но, как вы сказали, он должен быть довольно общим.

SELECT * FROM parent p
WHERE p.id in(
    SELECT r.parent_Id 
    FROM relationship r 
    WHERE r.parent_id in(1,2) 
    GROUP BY r.parent_id
    HAVING COUNT(r.parent_Id)=2
)

и число 2 в COUNT(r.parent_Id)=2 соответствует количеству соединений, которые вам нужны)

Если бы вы могли поместить свой список значений other_id в таблицу, это было бы идеально. Приведенный ниже код ищет родителей, у которых есть, ПО КРАЙНЕЙ МЕРЕ, указанные идентификаторы. Если вы хотите, чтобы у него были ТОЧНО те же идентификаторы (т.е. без дополнений), вам придется немного изменить запрос.

SELECT
     p.id,
     p.name
FROM
     My_Other_IDs MOI
INNER JOIN Relationships R ON
     R.other_id = MOI.other_id
INNER JOIN Parents P ON
     P.parent_id = R.parent_id
GROUP BY
     p.parent_id,
     p.name
HAVING
     COUNT(*) = (SELECT COUNT(*) FROM My_Other_IDs)