Задавайте вопросы, мы ответим
Вы не зашли.
Страниц: 1
Доброе время суток, уважаемые!
На днях пришлось немного помучиться и «написать» вот такой страшный и ужасный запрос.
Прежде чем читать далее:
- это не «кричащая» задача – она уже сделана как есть и работает без сбоев
- это больше просьба дать совет как оптимизировать, найти новые подходы, о которых я не знал
- это, как по мне, что-то вроде головоломки.
Если не охото «ломать» голову – можно не читать далее, НО я все же хотел бы найти иной, более оптимальный и «красивый» способ решения.
И еще – я даже с удовольствием вышлю бутылку замечательного вина, если кто предложит кардинально иной и очень лаконичный, и красивый метод.
Изначально задача стояла немного иная – и запрос был красивым и приятным, но к этому разделу постоянно изменялись требования и расширялись условия.
Этот фрагмент реализовывался под Друпал (со всеми вытекающими из этого подходами – типа основываемся на перехватах и не лезем в сторонний код, формируем вывод через систему theme, и таблицы тоже с «пейдженаторами» в отдельном своем модуле).
Так вот, ближе к «телу» - запрос (как в коде):
$sql="
Отредактированно ILYA (13.07.2010 19:58:11)
Неактивен
Выглядит конечно ужасно. Что сразу бросилось в глаза при беглом осмотре
1. "field_physicians_street_value longtext" Не поверю, что это поле и многие другие в таблице content_type_physicians нуждается в таком типе данных (longtext). Скорее всего будет за глаза достаточно varchar(255).
2. LEFT JOIN {url_alias} as ua ON ua.src = CONCAT('node/',n.nid) - тут без всякого анализа понятно, что индексы использованы не будут. Если есть возможность нужно в поле url_alias.src отрезать слева node/ , далее сохранять в базу без него, а джойнить напрямую ua.src = n.nid. Либо в таблицу node добавить колонку, где хранить "node/{nid}" и джойнить с этим полем
3. Приведите EXPLAIN итогового запроса, это первое что стоит делать при попытках оптимизации. Запрос может выглядеть очень страшно, но быстро работать, если индесы расставлены верно, а это explain и покажет. Приведите - подумаем дальше
P.S. WHERE 1 я тоже использую, удобно, правда WHERE 1=1, так просто нагляднее
Отредактированно Shopen (13.07.2010 23:03:34)
Неактивен
Shopen написал:
1. "field_physicians_street_value longtext"
Совершенно верно – достаточно varchar(255), но этот проект на Друпале. В конкретном случае мы не можем отходить от идеологии невмешательства в ядро и сторониие модули системы.
Другими словами – не принято вносить какие либо правки в ядро системы, сторонние модули, или в структуру базы, если не ты эти таблицы создаешь. Это нарушает плановое обновление ядра и модулей всех проектов.
Поэтому повлиять на это мы из идеологических соображений не можем.
Shopen написал:
2. LEFT JOIN {url_alias} as ua ON ua.src = CONCAT('node/',n.nid)
На счет url_alias – тоже абсолютно согласен, но поскольку алиас может быть не только к ноду, то соответственно от этого отказаться нельзя. И приходиться использовать достаточно энергоемкую функцию CONCAT('node/',n.nid). Я когда-то пробовал создавать временную таблицу из списка только нодовых альясов (“node/###”) и заполнять ее значениями nid. Использовал при этом LIKE ‘%node%’, что тоже энергоемко. То есть – не спасает, а только усложняет.
Отредактированно ILYA (14.07.2010 12:59:06)
Неактивен
Shopen написал:
3. Приведите EXPLAIN итогового запроса
Да - конечно:
(так как на этом проекте хостинг без ssh, то удобоваримый вид могу предоставить только так)
Отредактированно ILYA (14.07.2010 00:04:43)
Неактивен
Я хотел бы уточнить свой вопрос (просьбу).
Я не смогу оптимизировать структуру таблиц и создавать вспомогательные постоянные таблицы тоже не выход.
Меня искренне беспокоят подзапросы (я никогда их не любил – и не полюблю), особенно несколько и, причем, в нескольких секциях.
Другими словами, что можно сделать с этими тремя «…(SELECT GROUP_CONCAT …» ?
А еще с этим – в секции WHERE при поиске (поиске на форме страницы) появляются:
«….(SELECT count(td2.name) FROM term_node as….». То есть – по одному подзапросу для specialty и languages (язык, которыми владеет врач – таксономические термы, а не язык выбираемого нода).
Вот секция WHERE целиком:
Отредактированно ILYA (14.07.2010 13:00:23)
Неактивен
Shopen написал:
WHERE 1 я тоже использую
Зачем, если не секрет? (какой вообще в этом смысл?)
Неактивен
Все крайне просто - сам запрос (в данном случае секция WHERE) формируется в несколько этапов, в зависимости от логики добавляются условии. Но может быть, что секция WHERE останется стандартной (выборка по умолчанию). Таким образом проще (быстрее и нагляднее) в самом начале присвоить переменной $where единицу (ну или 1=1 - как кому нравиться) и затем (по ходу скрипта) добавлять $where .= "AND 'новое условие'"; и так далее.
Второй вариант - перед окончательной "сборкой" запроса (формируем $sql) проверить пустая ли переменная, содержится в ней что либо или нет (а также нужно проверять - первое ли это добавление в переменную - следует ли ставить вначале "AND", или по другому - ставить "AND" в конец каждого условия, но затем опять же проверить, что бы не остался "зависший" AND в конце) - но это дополнительные проверки, которые нагромождают скрипт, делая его менее лаконичным и читабельным.
Вот и все - все дело в удобстве.
LazY , а что на счет этих ненормальных подзапросов?
Отредактированно ILYA (15.07.2010 00:44:01)
Неактивен
ILYA написал:
Shopen написал:
3. Приведите EXPLAIN итогового запроса
Да - конечно:
(так как на этом проекте хостинг без ssh, то удобоваримый вид могу предоставить только так)
Как видите не используются идексы в одном объединении. И по сравнению с этим подзапросы - фигня, в плане производительности. Ваш запрос приводит к перебору 800 тысяч строк, тогда как с индексом было бы 22 тысячи (в идеале). Что мешает вам добавить индексированную колонку "node/{nid}" в таблицу node (обновляемую например триггером) и джойнить по ней? Если идеология, то надо решить что вам важнее идеология или скорость. Все CMS - это универсальные ящики, которые как и всё универсальное, плохо работают на узких задачах и их приходится допиливать под себя.
Конструкцию
Отредактированно Shopen (15.07.2010 19:53:37)
Неактивен
LazY написал:
Shopen написал:
WHERE 1 я тоже использую
Зачем, если не секрет? (какой вообще в этом смысл?)
Типичная ситуация, когда запрос генерируется скриптом (нотация парсера, но думаю понятно):
Неактивен
Shopen написал:
Конструкцию
SELECT ... WHERE 1 AND... AND (SELECT COUNT(*) FROM ...) !=0
имеет смысл переписать такSELECT ... WHERE 1 AND... AND EXISTS (SELECT 1 FROM ... LIMIT 1)
Спасибо - просто и понятно - совершенно верно - ведь нужно всего лишь до первого нахождения. Обязательно нужно будет поправить...
Shopen написал:
добавить индексированную колонку "node/{nid}" в таблицу node
Ммм.... Интересная идея! Спасибо за нее отдельное - нужно будет попробовать. И между прочим, если принять за правило - то это достаточно не тяжело реализовать. Сама таблица node несет основные данные по контенту и поэтому при любом обновлении системы она меняться не будет (более чем уверен) - поэтому все нормально. Выборка на альясы используется, по ходу, достаточно часто - поэтому можно ее практически продублировать в таблице node, но индексированное только src (то, где записано node/nid).
А обновлять можно тригером, а можно в перехвате (hook_nodeapi по операциям inser/update) в отдельном модуле (это уже нюансы...)
Другими словами, Shopen спасибо - все варианты попробую.
Shopen написал:
детские разговоры
Замете - фразы уже давно нет.
Отредактированно ILYA (15.07.2010 23:49:16)
Неактивен
ILYA написал:
Shopen написал:
добавить индексированную колонку "node/{nid}" в таблицу node
Ммм.... Интересная идея! Спасибо за нее отдельное - нужно будет попробовать.
Я ее вам еще в предыдущем сообщении написал, пропустили?
ILYA написал:
Замете - фразы уже давно нет.
Да, заметил, но слово не воробей. Впрочем забудем об этом.
Попробуйте эти оптимизации и сообщите результат, интересно.
Неактивен
После изменения в секции WHERE (с использованием ... AND EXISTS (SELECT 1 FROM ... LIMIT 1))
Время сократилось до полсекунды (0.50- 0.52 sec) – было полторы секунды.
После обновления структуры таблицы node (добавления индексного поля src, которое содержит “node/nid”) – время выполнения 0.45 sec.
Shopen написал:
Я ее вам еще в предыдущем сообщении написал, пропустили?
Та нет – не пропустил, но не предал такого значения (пока не увидел цифры, а затем сам не пересчитал) – в этой части мне не нравилась только операция конкатенации, но теперь я от нее тоже избавился.
Вот сам конечный запрос:
Отредактированно ILYA (16.07.2010 14:36:17)
Неактивен
И еще: чуть позже я уменьшил размерность для src в таблице node
С varchar(255) до varchar(32) . Этого более чем достаточно будет...
Неактивен
ILYA написал:
И тут у меня вопрос в отношении ключа src_language_pid (key_len = 386) – кажется это тоже можно «улучшить»?
Можно, прироста это все равно большого не даст. У вас там тоже longtext в этом поле?
Больше очевидного не вижу, но поэкспериментировать можно, имея данные на руках, например я бы добавил индекс в term_data PRIMARY KEY (tid, vid), чтобы избавится от range.
Неактивен
Спасибо за ответы.
С практической точки зрения (в смысле - не изменяя структур таблиц) - мне очень понравилась
Неактивен
ILYA написал:
Также добавив индексированное поле по src в таблицу node я думаю прирост производительности по сути из-за отказа от CONCAT (все же - строковая операция...), но это я так думаю - могу ошибаться (ну... тоесть, я думаю, что прирост производительности был в "этом месте" не из-за индексированного поля... - то есть, не только из-за него).
дело не в том что она строковая, дело в том что она - функция. Поскольку любая функция в подобной ситуации работает со значением каждой строки - использование индексов в принципе невозможно. С другой стороны если поле неиндексированное, то есть там конкатенация или нет - пофигу, не считая конечно затрат на саму операцию, но они мизерные.
ILYA написал:
А со значениями (длиной) текстовых полей конечно поиграться можно - но разве что на этом проекте - ради интереса, а так, обычно, приходиться отказываться от более важных задач и решений из-за нехватки времени и переводить уже в состояние "поддержки"...
Еще раз спасибо.
Пожалуйста, а что с range - не стали оптимизировать?
Неактивен
Страниц: 1