Доработка запросов движка 1 пользователь проголосовал

[art-dle 0]

Посмотрев, что пишется в slow_log MySQL я обнаружил кучу запросов вида:

SELECT * FROM

SELECT ... COUNT(*) ...

Вопрос - почему в 8(!) версии продукта все еще не используются запросы с индексированными полями в выборках и\или условиях?

Это вопрос скорости работы скрипта и нагрузки на сервер.

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

[WWW.ZEOS.IN 1 161]

Если ваша тема начинается с вопроса и вам нужна какая либо помощь, то в самой теме в обязательном порядке вы должны указывать ссылку на ваш сайт. Если ваш сайт находится в локальной сети и вы не можете предоставить ссылку то отправляйте персональное сообщение с вопросом в службу поддержки непосредственно с сайта http://dle-news.ru/, вам ответят на ваш вопрос в персональном порядке, в случае если пользуетесь легальной копией скрипта. Если вы не указали сайт, то ваша тема будет закрыта, а аккаунт на форуме заблокирован.

[art-dle 0]

Если ваша тема начинается с вопроса и вам нужна какая либо помощь, то в самой теме в обязательном порядке вы должны указывать ссылку на ваш сайт. Если ваш сайт находится в локальной сети и вы не можете предоставить ссылку то отправляйте персональное сообщение с вопросом в службу поддержки непосредственно с сайта http://dle-news.ru/, вам ответят на ваш вопрос в персональном порядке, в случае если пользуетесь легальной копией скрипта. Если вы не указали сайт, то ваша тема будет закрыта, а аккаунт на форуме заблокирован.

я хостер, на серверах которого располагается около сотни сайтов на движке ДЛЕ (те, что мы отследили при повышении посещаемости и создании нагрузки). Перечислять их нет смысла.

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

Отсутствие ответа и блокировку можно считать игнорированием в активной форме и сокрытием информации разработчиками от своих клиентов.

Теперь поясните, какова может быть причина (не формальная, а реальная) закрытия аккаунта, с которого написан единственный вопрос, легитимно, информация актуальна и достоверна в этом вопросе, вопрос имеет полное право быть заданным, стилистика выдержана на уровне социальной корректности, и форма вопроса не задевает ничьих интересов?

Меня интересует решение технического вопроса или точка зрения разработчиков на поставленный вопрос. А так же всех тех, кто использует движок. Это опыт, ознакомиться с которым - одна из задач опроса. Т.к. это реальный опыт использования продукта (если конечно все подряд не начнут в шутку отвечать). Но мне кажется, что здесь должны собраться достаточно адекватные пользователи.

Судя по количеству ваших постов - вы активный и давний участник форума. И, наверное, пользователь движка. Прошу ответить на опрос - мне интересно ваше мнение.

Изменено 21 марта 2010 пользователем art-dle

[celsoft 6 068]

Желательно голосовать тем, кто имеет представление об sql и оптимизации скриптов, кто разрабатывает на sql.

выборка по индексированным полям это то что стоит после WHERE, а не до. Как вы об этом сейчас пишите, и скрипт осуществляет выборку исключительно по индексам.

Теперь поясните, какова может быть причина (не формальная, а реальная) закрытия аккаунта, с которого написан единственный вопрос, легитимно, информация актуальна и достоверна в этом вопросе, вопрос имеет полное право быть заданным, стилистика выдержана на уровне социальной корректности, и форма вопроса не задевает ничьих интересов?

ваш аккаунт никто закрывать не собирается

SELECT ... COUNT(*) ...

запрос SELECT COUNT(*) намного быстрее чем SELECT COUNT(поле) т.к. подчитываются только количество полей, без выборки полей. Это так для информации. О чем кстати написано в документации по MySQL, разработчиками MySQL

[art-dle 0]

Желательно голосовать тем, кто имеет представление об sql и оптимизации скриптов, кто разрабатывает на sql.

выборка по индексированным полям это то что стоит после WHERE, а не до. Как вы об этом сейчас пишите, и скрипт осуществляет выборку исключительно по индексам.

далеко не всегда. в данный момент я отработал с очередным slow_log на полтора гигабайта на почти пустом сервере и единственным сайтом на этом движке. и еще момент join без индексов. но это будет уже следующий вопрос.

SELECT ... COUNT(*) ...

запрос SELECT COUNT(*) намного быстрее чем SELECT COUNT(поле) т.к. подчитываются только количество полей, без выборки полей. Это так для информации. О чем кстати написано в документации по MySQL, разработчиками MySQL

количество полей или записей?

[celsoft 6 068]

далеко не всегда. в данный момент я отработал с очередным slow_log на полтора гигабайта на почти пустом сервере и единственным сайтом на этом движке. и еще момент join без индексов. но это будет уже следующий вопрос.

Всегда. Вы полностью запрос приводите, из него будет все будет все видно, DLE является полностью настраиваемым в настройках скрипта и не исключено что там просто включены возможности которые не могут быть использованы без MySQL функций в выборке, при использовании функций в выборке ключи не используются самим MySQL, и это не значит что это проблема в неоптимизированных запросах, это значит что попросту невозможно выполнить запрос по другому, все такие запросы отключаются в настройках скрипта, и администратор сайта их решил не отключать

количество полей или записей?

записей

[art-dle 0]

далеко не всегда. в данный момент я отработал с очередным slow_log на полтора гигабайта на почти пустом сервере и единственным сайтом на этом движке. и еще момент join без индексов. но это будет уже следующий вопрос.

Всегда. Вы полностью запрос приводите, из него будет все будет все видно, DLE является полностью настраиваемым в настройках скрипта и не исключено что там просто включены возможности которые не могут быть использованы без MySQL функций в выборке, при использовании функций в выборке ключи не используются самим MySQL, и это не значит что это проблема в неоптимизированных запросах, это значит что попросту невозможно выполнить запрос по другому, все такие запросы отключаются в настройках скрипта, и администратор сайта их решил не отключать

количество полей или записей?

записей

даже в случае, когда запрос агрегированный и в выборке перечислены еще поля? вы считаете, что встроенный оптимизатор сервера справляется с этой задачей лучше при *

пожалуйста, приведите ссылку на информацию от разработчиков MySQl по этому вопросу. вы ссылались на нее ранее. благодарю

[celsoft 6 068]

art-dle,

на английскую документацию, я уже не помню ссылку, а вот на русском пожалуйста http://phpclub.ru/detail/article/mysql_optimize

Почему COUNT(*) обычно быстрее COUNT(id), поясню на примере:

Есть таблица message: id | user_id | text

с индексом PRIMARY(id), INDEX(user_id)

Нам надо подсчитать сообщения пользователя с заданым $user_id

Сравним 2 запроса:

SELECT COUNT(*) FROM message WHERE user_id = $user_id

и

SELECT COUNT(id) FROM message WHERE user_id = $user_id

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

Для выполнения второго запроса мы сначала проходим по индексу, для отбора записей удовлетворяющих условию, после чего если запись попадает под условие, то вытаскиваем ее (запись скорее всего будет на диске) чтобы получить значение id и только потом инкриментим счетчик.

В итоге получаем, что при большом кол-ве записей скорость первого запроса будет выше в разы.

даже в случае, когда запрос агрегированный и в выборке перечислены еще поля?

какие поля? все запросы в DLE где стоит COUNT(*) ничего другого больше нет и выборка других полей не производится.

[art-dle 0]

art-dle,

на английскую документацию, я уже не помню ссылку, а вот на русском пожалуйста http://phpclub.ru/detail/article/mysql_optimize

Почему COUNT(*) обычно быстрее COUNT(id), поясню на примере:

Есть таблица message: id | user_id | text

с индексом PRIMARY(id), INDEX(user_id)

Нам надо подсчитать сообщения пользователя с заданым $user_id

Сравним 2 запроса:

SELECT COUNT(*) FROM message WHERE user_id = $user_id

и

SELECT COUNT(id) FROM message WHERE user_id = $user_id

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

Для выполнения второго запроса мы сначала проходим по индексу, для отбора записей удовлетворяющих условию, после чего если запись попадает под условие, то вытаскиваем ее (запись скорее всего будет на диске) чтобы получить значение id и только потом инкриментим счетчик.

В итоге получаем, что при большом кол-ве записей скорость первого запроса будет выше в разы.

даже в случае, когда запрос агрегированный и в выборке перечислены еще поля?

какие поля? все запросы в DLE где стоит COUNT(*) ничего другого больше нет и выборка других полей не производится.

во-первых - статья очень старая, не учитывает изменения ноых версий псоле 4);

во-вторых - автор допустил некоторые ошибки, на что ему даже в комменты написали;

в-третьих - статья несколько некорректна. т.к. не ссылается на движок базы - myisam и innodb совсем по-разному отрабатывают такие вещи (table level и row level locking а также хранение метаинформации в myisam);

оригинал от разработчиков mysql был бы познавательнее и корректнее.

почему упомянул innodb - есть такие базы у сайтов с dle. часть баз на myisam, но есть и innodb. наверное из соображений надежности сменили структуру.

теперь о самих выборках - если б не group by и order by в них - они были бы легче

один из примеров:

SELECT DATE_FORMAT(date,'%b %Y') AS m_date, COUNT(*) AS cnt FROM dle_post WHERE approve AND date < '2010-03-21 04:30:29' GROUP BY m_date ORDER BY date desc;

план запроса:

Using where; Using temporary; Using filesort

в кеш он не попадет, постоянно дергается таблица на чтение

и еще, например, почему бы не использовать для таблицы dle_online тип MEMORY?

и еще есть вопросы, пока анализирую понемногу логи мускуля, набираются

[celsoft 6 068]

во-вторых - автор допустил некоторые ошибки, на что ему даже в комменты написали;

нет, не допустил, комментатор неверно интерпретирует аннотации, которые говорят что запрос будет быстрее если отсутствует в выборке другие поля и WHERE, это и так понятно, а не говорит о том что COUNT(поле) быстрее COUNT(*)

в-третьих - статья несколько некорректна. т.к. не ссылается на движок базы - myisam и innodb совсем по-разному отрабатывают такие вещи (table level и row level locking а также хранение метаинформации в myisam);

DLE использует myisam и для него данное утверждение верно

почему упомянул innodb - есть такие базы у сайтов с dle. часть баз на myisam, но есть и innodb. наверное из соображений надежности сменили структуру.

оригинальный скрипт не предусматривает innodb и все его запросы оптимизированы для myisam и менять вручную без изменения запросов в принципе неккоректно

теперь о самих выборках - если б не group by и order by в них - они были бы легче

один из примеров:

SELECT DATE_FORMAT(date,'%b %Y') AS m_date, COUNT(*) AS cnt FROM dle_post WHERE approve AND date < '2010-03-21 04:30:29' GROUP BY m_date ORDER BY date desc;

Понимаете это уже начинается если бы, да кабы. Если бы этого запроса вообще небыло, то было бы еще лучше, он не может быть без group by и order by, потому что это необходимо, да это не легкий запрос, но он кешируется самим скриптом и выполняется пару раз в сутки, вам сервер не в состоянии выполнить этот запрос пару раз в сутки? Так это не проблема скрипта, это уже проблема сервера. Далее этот запрос у меня выполняется за

Отображает строки 0 - 29 (45 всего, запрос занял 0.0001 сек.)

при изменении COUNT(*) на COUNT(id) не меняется ничего все точно также 0.0001 сек и план запроса Using where; Using temporary; Using filesort. Нет абсолютно никакой разницы, и это практика, а не теория, а говорить о том что "если бы небыло" нет никакого смысла, чтобы небыло нужно зайти в настройки скрипта и отключить, там такая возможность есть. База данных при этом не одна сотня тысяч новостей.

и еще, например, почему бы не использовать для таблицы dle_online тип MEMORY?

такой таблицы в DLE нет в принципе, это сторонний модуль, более того способный положить любой сервер, но это уже не ко мне, и не к DLE, он такой нагрузки не создает