Впервые я столкнулся с проблемой использования EXISTS, которую я не понимаю. У меня есть таблица с идентификатором пациента и датой консультации, и я пытаюсь выбрать тех пациентов, у которых есть несколько записей с одной и той же датой консультации:
-- total rows in vital: 732,527
-- number of rows after selection: 7,817
-- number of rows with multiple pat_id/kons_dt: 809
SELECT a.*
FROM emr.vital a
WHERE EXISTS (SELECT 1
FROM (SELECT pat_id, kons_dt, COUNT(*)
FROM emr.vital b
GROUP BY pat_id, kons_dt
HAVING COUNT(*) > 1
) b
WHERE a.pat_id = b.pat_id
)
Это дает мне следующий результат (только первые 4 строки):
+--------+---------------+
| pat_id | kons_dt |
+--------+---------------+
| 21384 | 2018-06-29 |
| 21384 | 2018-06-29 |
| 21888 | 2017-04-04 |
| 21888 | 2017-04-04 |
| ... | ... |
+--------+---------------+
Таблица emr.vital не имеет индекса. Если я добавлю индекс:
CREATE INDEX id ON emr.vital(pat_id);
и запустить тот же код, запись не выбрана. Я попытался создать минимальный пример, но у меня не получилось получить одинаковые результаты, то есть я получил одинаковые результаты для обоих случаев с индексом и без него.
Возможно, выдержка EXPLAIN может дать некоторые идеи:
Без индекса:
+----+--------------+-------------+--------+---------------+--------------+---------+-----------+--------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+-------------+--------+---------------+--------------+---------+-----------+--------+---------------------------------+
| 1 | PRIMARY | a | ALL | \N | \N | \N | \N | 734988 | Using filesort |
| 1 | PRIMARY | <subquery2> | eq_ref | distinct_key | distinct_key | 7 | func,func | 1 | |
| 2 | MATERIALIZED | <derived3> | ALL | \N | \N | \N | \N | 734988 | |
| 3 | DERIVED | b | ALL | \N | \N | \N | \N | 734988 | Using temporary; Using filesort |
+----+--------------+-------------+--------+---------------+--------------+---------+-----------+--------+---------------------------------+
и с индексом:
+----+-----------------+-------------+--------+---------------+--------------+---------+----------------------------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-----------------+-------------+--------+---------------+--------------+---------+----------------------------+--------+----------------------------------------------+
| 1 | PRIMARY | a | ALL | pat_id | \N | \N | \N | 734988 | Using filesort |
| 1 | PRIMARY | <subquery2> | eq_ref | distinct_key | distinct_key | 4 | func | 1 | |
| 2 | MATERIALIZED | <derived3> | ALL | \N | \N | \N | \N | 734988 | |
| 3 | LATERAL DERIVED | b | ref | pat_id | pat_id | 5 | emr.a.pat_id | 1 | Using where; Using temporary; Using filesort |
+----+-----------------+-------------+--------+---------------+--------------+---------+----------------------------+--------+----------------------------------------------+
Любая помощь приветствуется. Версия MariaDB: 10.4.8.
Изменить 1: это структура исходной таблицы без индекса на pat_id.
CREATE TABLE `vital` (
`pat_id` int(10) unsigned DEFAULT NULL,
`kons_dt` date DEFAULT NULL,
`praxis_id` int(10) unsigned DEFAULT NULL,
`sex` varchar(10) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
`doby` smallint(6) DEFAULT NULL,
`age_y` smallint(6) DEFAULT NULL,
`gewicht` double DEFAULT NULL,
`groesse` double DEFAULT NULL,
`bmi` double DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
