Технический сбой

Сайт временно работает в режиме только для чтения, идут восстановительные работы. Приносим свои извинения за неудобства.
DLE FAQ » Все вопросы » MySQL » Как оптимизировать запрос MySQL?

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

     Все вопросы » MySQL    2059

вопрос
Имеется сайт на DLE 14.0 и база на > 100 000 записей. Если перейти к примеру на 8000 страницу, то запрос
[query] => SELECT p.id, p.autor, p.date, p.short_story, CHAR_LENGTH(p.full_story) as full_story, p.xfields, p.title, p.category, p.alt_name, p.comm_num, p.allow_comm, p.fixed, p.tags, e.news_read, e.allow_rate, e.rating, e.vote_num, e.votes, e.view_edit, e.editdate, e.editor, e.reason FROM dle_post p LEFT JOIN dle_post_extras e ON (p.id=e.news_id) WHERE approve=1 AND allow_main=1 ORDER BY date DESC LIMIT 71550,10
[time] => 3,8447678089142
[num] => 1

занимает аж 3,8 секунды, что несоизмеримо много, если бы этот запрос был выполнен, например, на 40 странице. Где-то читал, что MySQL плохо работает с большими выборками по LIMIT. Как его можно оптимизировать?

Ответа пока нет

 

26 комментариев

voronoff_dev
Юзер

voronoff_dev - -

Вот статья как раз под ваш случай https://habr.com/ru/post/217521/
qwerty123123
Юзер

qwerty123123 - -

Спасибо сейчас гляну
dj-avtosh
PHP-developer

dj-avtosh - -

Здравствуйте, запрос тормозит скорее всего из-за текстовых полей (они не индексируются): 
p.short_story, CHAR_LENGTH(p.full_story) as full_story



Попробуйте их отбросить и с использованием SQL_NO_CACHE повторить запрос и посмотреть время выполнения.
По заказам пишем сюда: @Rud00y

ЯД: 41001679231462
Заказы в telegram (ремонт модулей, оптимизация нагрузок и т.п.):
В телегу писать сразу задачу и бюджет.
qwerty123123
Юзер

qwerty123123 - -

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

Может имеет смысл как-то переписать логику кода с использованием PHP, например, такой запрос выполняется практически мгновенно:
[query] => SELECT p.id, p.autor, p.date, p.short_story, CHAR_LENGTH(p.full_story) as full_story, p.xfields, p.title, p.category, p.alt_name, p.comm_num, p.allow_comm, p.fixed, p.tags, e.news_read, e.allow_rate, e.rating, e.vote_num, e.votes, e.view_edit, e.editdate, e.editor, e.reason FROM dle_post p LEFT JOIN dle_post_extras e ON (p.id=e.news_id) WHERE approve=1 AND allow_main=1 AND id > 78000 ORDER BY date DESC LIMIT 10
[time] => 0,0016498565673828
dj-avtosh
PHP-developer

dj-avtosh - -

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

Затем попробуйте выполнить запрос такой и напишите сюда время выполнения:

SELECT SQL_NO_CACHE p.id FROM dle_post p LEFT JOIN dle_post_extras e ON (p.id=e.news_id) WHERE approve=1 AND allow_main=1 ORDER BY date DESC LIMIT 71550,10
По заказам пишем сюда: @Rud00y

ЯД: 41001679231462
Заказы в telegram (ремонт модулей, оптимизация нагрузок и т.п.):
В телегу писать сразу задачу и бюджет.
qwerty123123
Юзер

qwerty123123 - -

Получилось около 0,47214794158936 с LIMIT 71550,10
dj-avtosh
PHP-developer

dj-avtosh - -

вот теперь нужно полученные отсюда id вставить в исходный запрос (который в заголовке этого вопроса) как WHERE p.id IN (список через запятую) и убрать limit.
По заказам пишем сюда: @Rud00y

ЯД: 41001679231462
Заказы в telegram (ремонт модулей, оптимизация нагрузок и т.п.):
В телегу писать сразу задачу и бюджет.
qwerty123123
Юзер

qwerty123123 - - 

SELECT p.id, p.autor, p.date, p.short_story, CHAR_LENGTH(p.full_story) as full_story, p.xfields, p.title, p.category, p.alt_name, p.comm_num, p.allow_comm, p.fixed, p.tags, e.news_read, e.allow_rate, e.rating, e.vote_num, e.votes, e.view_edit, e.editdate, e.editor, e.reason FROM dle_post p LEFT JOIN dle_post_extras e ON (p.id=e.news_id) JOIN (SELECT id FROM dle_post WHERE allow_main=1 AND approve=1 ORDER BY date DESC LIMIT 85150,10) as l ON p.id=l.id

Такой запрос отработал за 0.2768 секунд. Конечно результат на лицо, но я думаю если в базе появится еще 200К записей, то эта цифра может перевалить за 1 секунду
dj-avtosh
PHP-developer

dj-avtosh - -

прочитайте внимательно что я вам предлагал. Сперва выполняете запрос первый и пооучаете id, потом вторрой, в который id вставляете.
По заказам пишем сюда: @Rud00y

ЯД: 41001679231462
Заказы в telegram (ремонт модулей, оптимизация нагрузок и т.п.):
В телегу писать сразу задачу и бюджет.
qwerty123123
Юзер

qwerty123123 - -

Первый запрос с выборкой ID выполнился за 0.2676, а второй с IN за 0.0006. Итого - 0,2682 секунд
dj-avtosh
PHP-developer

dj-avtosh - -

вот такими двумя запросами нагрузки и не будет в итоге даже при миллионе, т.к. в ход будет идти индекс.
По заказам пишем сюда: @Rud00y

ЯД: 41001679231462
Заказы в telegram (ремонт модулей, оптимизация нагрузок и т.п.):
В телегу писать сразу задачу и бюджет.
dj-avtosh
PHP-developer

dj-avtosh - -

тот человек, который вам написал про id > вряд ли дружит с логикой, не факт что в вашей таблице все начинается с id 1.
По заказам пишем сюда: @Rud00y

ЯД: 41001679231462
Заказы в telegram (ремонт модулей, оптимизация нагрузок и т.п.):
В телегу писать сразу задачу и бюджет.
qwerty123123
Юзер

qwerty123123 - -

Согласен с вами.
А если переделать пагинацию и, например, передавать в ссылке ID публикации на которой остановился пользователь (убрав страницы и сделав кнопки вперед/назад)?
cortin
Юзер

cortin - -

Я решал такие вопросы через WHERE ID > .... LIMIT 50
qwerty123123
Юзер

qwerty123123 - -

Спасибо за подсказку, сейчас тоже подумал об этом. Но как лучше получить ID начального поста страницы?
Мне пока только приходит вариант сначала выбрать все ID публикаций и закэшировать их.
cortin
Юзер

cortin - -

SELECT p.id FROM dle_post p LEFT JOIN dle_post_extras e ON (p.id=e.news_id) WHERE approve=1 AND allow_main=1 ORDER BY date DESC LIMIT 1
cortin
Юзер

cortin - -

но как уже писали выше, пагинацию делать надо иначе, мне для крона это подходило, чтобы сделать пагинацию такую хитрую, надо подумать :) я пока мимо, домой поеду :)
cortin
Юзер

cortin - -

Хотел добавить на больших базах, мы например ограничивали пагинацию, макс 200 страниц ставили, иначе гугл индекс заходит в глубь и начинаются тормоза, ведь пока mysql подгрузит с диска все пока обработает... ух. Конечно если второй раз зайти то все быстро ведь уже все в mysql кэше, а так только WHERE ID > ... помогает
lutskboy
Эксперт

lutskboy - -

согласен на счет WHERE ID >...
сам такое делал. только без пагинации. у меня было кнопка типа "Показать еще..."
так совсем нет никакой нагрузки даже если там миллионы записей. правда какой дурак будет нажимать на ету кнопку чтоб добраться до последних записей
такой вариант идеален для чата чтоб получить историю переписки скажем 5 лет назад

в вашем случае лучше ограничить например 1000страниц
cortin
Юзер

cortin - -

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

recordsPerPage = 50;
for( page = 1; page < 200; page++ )
{
    if ( page == 1 )
    {
        pageID = SELECT id FROM dle_posts ORDER BY id DESC LIMIT 1;
    }else{
        pageID = SELECT id FROM dle_posts WHERE id < prevID ORDER BY id DESC LIMIT recordsPerPage, 1;
    }
    // tut formiruem paginaciju dlja stranici, naprimer https://site.com/page/<pageID>
    prevID = pageID;
}
qwerty123123
Юзер

qwerty123123 - -

Спасибо за пример, но все-таки я думаю лучше действительно ограничить выдачу, так как, например, при выборе новостей в категориях используется немного другая логика. По хорошему, конечно, лучше переделать структуру БД, но это может конфликтовать с будущими обновлениями движка.
cortin
Юзер

cortin - -

подключите сфинкс
dj-avtosh
PHP-developer

dj-avtosh - -

тогда лучше уж postgres?
По заказам пишем сюда: @Rud00y

ЯД: 41001679231462
Заказы в telegram (ремонт модулей, оптимизация нагрузок и т.п.):
В телегу писать сразу задачу и бюджет.
cortin
Юзер

cortin - -

Еще хотел добавить что на больших базах придется использовать memcache, redis ( его нет но можно установить ) и допилить эти страници через кеш, чтобы в бэкграунде крутился один процесс которые все эти страница -> ид находил, в одном потоке это уже не нагрузка. а остальные будут уже бегать по страницам, для это нужно или кроном или без крона через popen запускать скрипт.

У нас стоят серьезные сервера, но все равно мы сталкнулись что на больших базах не все в памяти и тормозит, но правильно созданый кэш это решение
dj-avtosh
PHP-developer

dj-avtosh - -

Почему это нет memcache?
По заказам пишем сюда: @Rud00y

ЯД: 41001679231462
Заказы в telegram (ремонт модулей, оптимизация нагрузок и т.п.):
В телегу писать сразу задачу и бюджет.
cortin
Юзер

cortin - -

я про редис, в дле 14 я не нашел сорсов где в качестве кеша используется редиска

Похожие вопросы

 11.05.2014 Iam_what_Iam  Общие вопросы по вёрстке, Шаблоны (TPL), MySQL
 14.04.2015 malenovski  Общие вопросы по PHP, MySQL
 11.05.2015 follor  MySQL
 09.06.2015 follor  Общие вопросы по PHP, MySQL
 26.10.2016 Kaylos_Poigraem  Общие вопросы по PHP, MySQL
 09.11.2016 violex  MySQL
 08.12.2016 DomiTori  MySQL, Ламерские вопросы
 08.12.2016 venrom  Общие вопросы по PHP, MySQL
 12.09.2017 Alenna  MySQL
 27.11.2019 websyndicate18  MySQL, Ламерские вопросы
  • Puxlik | Telegram Sender 13
    Фото Puxlik
    Puxlik
    Telegram Sender
    Версию 17.1 поддерживает ?
  • TeraMoune | Не загружается изображение [Ошибка HTTP] ... 2
    Фото TeraMoune
    TeraMoune
    Не загружается изображение [Ошибка HTTP]. Как исправить?
    В этой версии есть фрагментация загрузки файла когда загружаемый файл разделяется на фрагменты, по умолчанию 1 фрагмент 1.5мб. (И у вас написано лимиты на файл 4мб и картинки 5.86мб, этого достаточно чтобы принимать 1.5мб фрагменты) И если бы была проблема с весом или чем-то подобным то об этом бы сервер сообщил. Сервер отдал 500 ошибку это значит что угодно, нужно смотреть ответ от сервера в консоли разработчика или логах.
  • RedFox | Не загружается изображение [Ошибка HTTP] ... 2
    Фото RedFox
    RedFox
    Не загружается изображение [Ошибка HTTP]. Как исправить?
    "Некоторые картинки загружаются, но большинство нет."

    Сначала не обратил внимания, но видимо проблема в этом. Все, что больше 100Кб, не загружается. Что странно, поскольку в php у меня следующие значения:
    post_max_size = 100M
    upload_max_filesize = 100M
    max_file_uploads = 20

    Полагаю, где-то еще надо увеличить размер загружаемой картинки, но где?
  • LMakc | Модуль читалка pdf 1
    Фото LMakc
    LMakc
    Модуль читалка pdf
    Не начинай, я заплатил сегодня штраф за підручники. Сейчас очень сложно с этим.
  • ssssss | Сделать подсчет количества символов в ко ... 5
    Фото ssssss
    ssssss
    Сделать подсчет количества символов в комментариях
    Есть актуальный плагин под 17 версию? (только количество символов! без наворотов... записей куда-либо и т.п.) *ББ-редактор
  • alex.sg.spb | Как вывести информацию о обновлении ново ... 8
    Фото alex.sg.spb
    alex.sg.spb
    Как вывести информацию о обновлении новости, но на время?
    вопрос, в продолжение поста. Помогите с кодом вывода инфо UPDATE на краткой новости, так чтобы не нужно было нажимать внутри новости при редактировании галочку?
  • Kentavr | Как вместо %20 в адресной строке подстав ... 9
    Фото Kentavr
    Kentavr
    Как вместо %20 в адресной строке подставлять символ + между словами ???
    Спасибо огромное. Этот вариант самый простой и рабочий!
  • derebas | Как вместо %20 в адресной строке подстав ... 9
    Фото derebas
    derebas
    Как вместо %20 в адресной строке подставлять символ + между словами ???
    К черту htaccess давайте проще

    к href добавьте id="xfield_value"
    <a href="/company/[xfvalue_company]/" id="xfield_value">[xfvalue_company]</a>

    И добавьте скрипт обработчик 
    <script>
document.addEventListener('DOMContentLoaded', function() {
    const links = document.querySelectorAll('a#xfield_value');

    links.forEach(link => {
      let href = link.getAttribute('href');

      if (href) {
        href = href.replace(/ /g, '+');
        link.setAttribute('href', href);
      }
    });
  });
</script>
  • Kentavr | Как вместо %20 в адресной строке подстав ... 9
    Фото Kentavr
    Kentavr
    Как вместо %20 в адресной строке подставлять символ + между словами ???
    Так сайт не работает вообще. Браузер выдает такое

    Страница недоступна Сайт ... выполнил переадресацию слишком много раз.
    Попробуйте удалить файлы cookie.

    ERR_TOO_MANY_REDIRECTS

    Куки чистил не помогает
  • derebas | Как вместо %20 в адресной строке подстав ... 9
    Фото derebas
    derebas
    Как вместо %20 в адресной строке подставлять символ + между словами ???
    Попробуйте так
    RewriteEngine On
    RewriteCond %{REQUEST_URI} %20
    RewriteRule (.*)%20(.*) $1+$2 [N]
    RewriteRule (.*) $1 [R=301,L]
наверх