Как оптимизировать sql запрос?

Question

[di]

Есть 2 таблицы -
statement(заявки)

id	email	snils	inn	date_create

cert_data(данные сертификатов)

id	number	prop	value

В таблице cert_data данные расположены вертикально,тоесть, например

id	number	prop	value
1	lalalala	email	[e-mail]
2	lalalala	snils	123123123123
3	lalala	inn	321321321321

Мне необходимо получить список id заявок(statement.id) и номеров сертификатов (cert_data.number), у которых

1. совпадают email
   
2. совпадают snils
   
3. совпадают inn
   
4. От времени заявки до создания сертификата прошло не более 60 дней
   

Важно! Одной заявке могут соответствовать несколько сертификатов. Мне нужны все. Поэтому LEFT JOIN не могу использовать.

Ну и я собственно говоря вписал все в один запрос:

SELECT `statement`.`id`,`cd1`.`number` FROM `statement`
            JOIN cert_data as cd1
            ON(cd1.value = `statement`.`email` AND cd1.prop = 'email')
            JOIN cert_data as cd2
            ON(cd2.value = `statement`.`organisation_inn` AND cd2.prop = 'inn')
            JOIN cert_data as cd3
            ON(cd3.value = `statement`.`snils` AND cd3.prop = 'snils')
            JOIN cert_data as cd4
            ON(cd4.prop = 'valid_from_time' AND cd4.value < (`statement`.`date_create` + 60*24*3600))
            WHERE `cd1`.`number` = `cd2`.`number` 
                  AND `cd2`.`number`=`cd3`.`number`
            AND statement.status='payment' AND (statement.number='0' OR statement.number='' OR statement.number IS NULL)

В таблице statement - 12000 записей, в таблице cert_data - 220000.

Работает этот запрос очень-очень долго.
Сначала памяти не хватило PHP PDO - отвалился с Fatal error...
Потом ждал минут 30, apache умер в openserver...Так и не дождался...
Если поставить LIMIT 50, то запрос выполняется 10 секунд.

Разбиваю все на 4 запроса(приведу 2 для примера)

SELECT `statement`.`id`,`cert_data`.`number` FROM `statement` 
             JOIN cert_data ON(cert_data.prop = 'snils'
               AND statement.status='payment' 
                  AND (statement.number='0' 
                  OR statement.number='' 
                  OR statement.number IS NULL) 
               AND cert_data.value = `statement`.`snils`;

            SELECT `statement`.`id`,`cert_data`.`number` FROM `statement` 
             JOIN cert_data 
             ON(
              cert_data.prop = 'inn' 
              AND statement.status='payment' 
              AND (statement.number='0' 
                  OR statement.number='' 
                  OR statement.number IS NULL)
              AND cert_data.value = `statement`.`organisation_inn`)"
        );

И оказывается, если разбить на 4 запроса и потом соединить результаты на серверном языке (а это php 5.6) то это будет быстрее. Но как так? Ведь все говорят что один запрос быстрее чем 4? Так ведь это всего то join 200000 записей, не миллионы же?
Подскажите возможно ли ускорить работу этого запроса, может неправильно составил?

Comments

[vman]

Начните с того, что добавьте перед запросом EXPLAIN, потом убедитесь, что в ключевых местах используются индексы, если их нет - создайте.

[di]

vman, Вот что выводит EXPLAIN
+----+-------------+-----------+------+---------------+------+---------+------+--------+--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+------+---------------+------+---------+------+--------+--------------------------------+
| 1 | SIMPLE | statement | ALL | NULL | NULL | NULL | NULL | 10689 | Using where |
| 1 | SIMPLE | cd1 | ALL | NULL | NULL | NULL | NULL | 218679 | Using where; Using join buffer |
| 1 | SIMPLE | cd2 | ALL | NULL | NULL | NULL | NULL | 218679 | Using where; Using join buffer |
| 1 | SIMPLE | cd3 | ALL | NULL | NULL | NULL | NULL | 218679 | Using where; Using join buffer |
| 1 | SIMPLE | cd4 | ALL | NULL | NULL | NULL | NULL | 218679 | Using where; Using join buffer |
+----+-------------+-----------+------+---------------+------+---------+------+--------+--------------------------------+
5 rows in set (0.00 sec)

[Alex]

WHERE `cd1`.`number` = `cd2`.`number`
AND `cd2`.`number`=`cd3`.`number`

перенеси в условия Join

[vman]

По всей видимости у вас нет ни одного индекса, поэтому идет полный скан таблиц.

ALTER TABLE cert_data ADD INDEX (number), ADD INDEX (prop), ADD INDEX (value);
ALTER TABLE statement ADD INDEX (status), ADD INDEX(number);

Повторяем EXPLAIN, в колонке possible_keys, key должны быть указаны созданные индексы.

[SharuPoNemnogu]

1. Поля в описании и в запросе различаются, не понятно где ошибка.
2. Лучше использовать 4 запроса соединенных через union.
3. Добавить составной индекс на cert_data(prop, value), по ним идет join
4. добавить индекс на date_create, или возможно составной на (status, date_create), explain покажет
5. структура таблиц не верная, раз приходится такие костыли лепить и джоинить по разным полям.

Если ничего не помогает, или помогает, но профита не достаточно, использовать пагинацию, но и она крякнется после 300к записей вроде.

[di]

vman, Спасибо. Помогло) Надо будет мне теорию почитать) Оптимизацией не занимался никогда, хотя мечтал бы на highload проекты попасть

[di]

Alex, Спасибо большое)

[di]

SharuPoNemnogu, Спасибо

1.Да, в statement - organisation_inn а не inn
2.Возможно, так и не попробовал, и с JOIN прокатило все
3-4. Индексы здорово помогли. Теперь за 2-3 секунды все выполняется и этого более чем достаточно
5.

Ну таблица cert_data так построена была из-за того что много полей у каждого сертификата может быть, чтобы постоянно не ловить ошибки и не править таблицу решили так ее развернуть, EAV реализовывать было лень....Да и легче бы не стало если бы появилась еще одна таблица для атрибутов и для значений.

Этот запрос будет выполняться раз в 2 недели, так что один раз можно и поджойнить) Суть в том что сначала создаются строки в statement, и только через определенное время выгружаются сертификаты в cert_data которые выданы по этим заявкам и их хотелось привязывать для удобства администратору