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

Question

[Вячеслав Беляев]

SELECT a.*, 
c.family, c.imya, c.otchestvo, c.phone,
u.`name` AS nameUslug, meet.id AS meet,  meet.dateMeet, meet.dateWasMeet, calls.id AS calls, calls.dateCall, 
office.demoName AS officeName, cl.id AS complaint, 
us1.name AS userCreated_name,
us2.name AS userDogovor_name,
us3.name AS userComplete_name
FROM ankets a 
LEFT JOIN clients c ON c.id = a.`client`
LEFT JOIN uslugi u ON u.id = a.usluga
LEFT JOIN meet  ON meet.id = a.meet
LEFT JOIN calls  ON calls.id = a.`call`
LEFT JOIN office  ON office.id = c.office

LEFT JOIN users us1 ON us1.id = a.userCreated
LEFT JOIN users us2 ON us2.id = a.userDogovor
LEFT JOIN users us3 ON us3.id = a.userComplete

LEFT JOIN anket_user au  ON au.anketa = a.id
LEFT JOIN anket_group ag  ON ag.anketa = a.id
LEFT JOIN complaint cl  ON cl.anketa = a.id AND cl.`status` = '0'
WHERE a.`status` = '1' AND a.`delete` = '0' 
AND ((au.`user` = '1' AND au.`status` = '1') OR (ag.`group` = '2' AND ag.`status` = '1' AND (c.`office` = '1' OR c.`office` = '3' OR c.`office` = '0')) )

Есть такой большой код, как можно его оптимизировать и ускорить?

Вот скрип детализации запроса gyazo.com/86c4737debbf4c10b203c3f612d185ad

Comments

[Swartalf]

Ну вы бы хоть бы план выполнения запроса предоставили что ли....

[Вячеслав Беляев]

Так я думаю тут план не сложный. Получаем записи из "ankets" и получаем по этим ankets.id дополнительные данные. И в условиях фильтруем по полученным данным

Answer 1

[Rsa97]

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

Comments

[Вячеслав Беляев]

План запроса получил, скриншот прикрепил к основному сообщению

[Rsa97]

Вячеслав Беляев: Теперь смотрите, у Вас есть строки, где possible_keys нулевые, это значит, что надо попробовать добавить индексы на поля этих таблиц, которые встречаются в условиях ON и WHERE.

[Вячеслав Беляев]

Ну это то что PRIMARY ? или index и primary это разное?

[Rsa97]

Вячеслав Беляев: PRIMARY - это первичный ключ, он всегда является уникальным индексом.

[Вячеслав Беляев]

и я того же мнения. На таблице "ankets" и на "complaint " у меня есть PRIMARY, а в скрине пишет что нету. На счет остальных там они не нужны . там всего несколько полей которые связывают таблицы

[Rsa97]

Вячеслав Беляев: Они просто не используются в запросе, поэтому EXPLAIN их не показывает. Например ankets находится в левой части JOIN'а то есть к текущей строке ankets добавляются строки других таблиц, поэтому ankets.id в ON используется без индексов.
Что касается остальных - посмотрите, например, на LEFT JOIN anket_user. Сейчас для каждой строки ankets перебираются все строки anket_user в попытке удовлетворить условию ON. Если добавить индекс на anket_user.anketa, то он будет использоваться и время выполнения сократится.
Аналогично, если для ankets составить комбинированный индекс (`status`, `delete`), то вместо перебора всех строк этот индекс, скорее всего, будет задействован и сразу будут выбраны нужные строки во WHERE.

[Вячеслав Беляев]

но anket_user.anketa не уникальное значение, если я поставлю индекс то это ускорит запрос?

Есть в WHERE добавить условия anket_user.status == '1' (отфильтрую неактивные привязки) это поможет?

[Rsa97]

Вячеслав Беляев: Уникальность здесь неважна, всё равно при использовании индекса перебор сокращается.
А условие a.status = 1 у Вас и так есть во WHERE.

[Вячеслав Беляев]

Rsa97: anket_user.status == '1' - активность привязки анкеты к сотруднику

таблицы идут формата MYISAM, как на них создать index? у меня выбор только PRIMARY, FULLTEXT, UNIQUE что мне выбрать?

[Вячеслав Беляев]

Добавил индексы таким запросом
ALTER TABLE `anket_user` ADD INDEX anketa (anketa); производительность заметно увеличилась, думаю я в правильном направлении. Спасибо

[Вячеслав Беляев]

Rsa97: А если такая ситуация. есть в таблице ankets.id и в money.anketa
Таблица money так же имеют свои id и тд. Нужно ли добавлять к money.anketa индекс?

[Rsa97]

Вячеслав Беляев: Если это поле используется в условиях JOIN ON, WHERE, GROUP BY, ORDER BY и т.д., то индекс желателен.

[Вячеслав Беляев]

Rsa97: А если я создам не там индекс это может привести к плачевным результатам?

[Rsa97]

Вячеслав Беляев: Нет. Обычный индекс может повлиять только на скорость выполнения запросов. Он замедляет INSERT/UPDATE запросы, если меняется поле, включённое в какой-либо из индексов и ускоряет SELECT, если сервер способен использовать этот индекс для ограничения выборки или быстрой сортировки.

[Вячеслав Беляев]

Rsa97: Все понял. Пока все чудестно работает не буду трогать) Еще раз спасибо

Answer 2

[nozzy]

Не понял что там с users,
может убрать два join-а :
LEFT JOIN users us1 ON us1.id = a.userCreated
LEFT JOIN users us2 ON us2.id = a.userDogovor
LEFT JOIN users us3 ON us3.id = a.userComplete

=>

LEFT JOIN users us1 ON us1.id IN (a.userCreated, a.userDogovor, a.userComplete)

Comments

[Вячеслав Беляев]

А вот это уже толковый совет, спасибо)

[_ _]

А если там три разных юзера? Один создал, второй заключил договор, третий закрыл. Вдруг нужно пользователя для каждой стадии отобразить?

[Rsa97]

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

[nozzy]

Остальное вроде все правильно.Очень много join-ов через left - это необходимость?Попробуйте где возможно inner - уберете лишние данные из результата.

[Вячеслав Беляев]

Александр Марченко: Да, там разные пользователи. Выводы по неудачному совету себе сделал)

[Вячеслав Беляев]

nozzy: с джойнами я не сильно дружу, поэкспериментирую с inner

Answer 3

[Wol_fi]

MYSQL джоинит таблицы через внутренние циклы. Такое количество джоинов - убийственно. Может вам стоит подумать о денормализации данных?

Comments

[Вячеслав Беляев]

Что значит "денормализации данных" ?

[Wol_fi]

Вячеслав Беляев: Хранить данные, которые вы получаете через джоины с других таблиц,в искомой. Например в ankets.userCreated хранить не users.id а сразу users.name. Такой подход увеличивает расходы на запись\обновление но уменьшает их при чтении. Подробнее можете почитать тут habrahabr.ru/post/64524 например.

[Вячеслав Беляев]

Wol_fi: это меня не устраивает. так как users.name может меняться и менять во всех таблицах его не хорощо

Answer 4

[German Zvonchuk]

Вячеслав Беляев если вы работаете на PHP, создайте один класс Questionary методом getQuestionary()
и дальше используйте примено так, передавайте критерии и получайте ответ:

$questionaryObj= Questionary->getQuestionary(array(
    'status' => 1,
    'delete' => 0,
));

Это вместо первого запроса к таблице ankets.
Далее крутим полученные данные и получаем оставшиеся данные:

foreach($questionaryObj as $questionary) {

    $clientObj= Client->getClient(array('cliendId' => $questionary->cliendId));
    $serviceObj= Service->getService(array('serviceId' => $questionary->serviceId));
    $meetObj= Meet->getMeet(array('meetId' => $questionary->meetId));
    $userCreatedName = User->getUser(array('userCreated' => $questionary->userId));
    $userDogovorName  = User->getUser(array('userDogovor' => $questionary->userDogovor));
    $userCompleteName  = User->getUser(array('userComplete' => $questionary->userComplete));

    $responceArr = array(
        'name' => $clientObj->name,
        'lastname' => $clientObj->lastname,
        'patronymic' => $clientObj->patronymic,
        'phone' => $clientObj->phone,
        'serviceName' => $serviceObj->name,
        'meet' => $meetObj->meet,
        'wasMeet' => $meetObj->wasMeet,
        'dateMeet' => $meetObj->dateMeet,
        'serviceName' => $serviceObj->name
    );
}

И в итоге собираете массив с нужными вами данными.
Плюс этого способа в том, что выполняются мелкие и простые SQL-запросы.
Большие и сложный запросы сложно поддераживать.

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