Меню
Что нового?

Запрос очень медленно обрабатывается.

FRIE

FRIE

Новичок
Сначала всё было гуд, а теперь в таблице около 8000 записей и запрос обрабатывается > 20 секунд.

PHP: 
$result = mysql_query("SELECT `id`,`user_id`,`word`,`type`,`date` FROM `words` WHERE `user_id` IN (SELECT `user_id` FROM `words` GROUP BY `user_id` HAVING COUNT(*) > 1 and `date` >= $from and `date` <= $to) ORDER BY `user_id`,`date`");
Есть вариант его облегчить?
 
c0dex

c0dex

web.dev 2002-...
Команда форума
Партнер клуба
Индексы все расставил?
 
c0dex

c0dex

web.dev 2002-...
Команда форума
Партнер клуба
Очень просто, ручками, через консоль mysql или через phpMyAdmin, вообще советую прочитать о том, как использовать EXPLAIN в MySQL, отпадет очень много вопросов, в часности твой "почему тормозит"
 
FRIE

FRIE

Новичок
c0dex написал(а):
Очень просто, ручками, через консоль mysql или через phpMyAdmin, вообще советую прочитать о том, как использовать EXPLAIN в MySQL, отпадет очень много вопросов, в часности твой "почему тормозит"
Нажмите для раскрытия...
Расставил индексы, стало работать быстрее, уже 13 секунд грузится
 
FRIE

FRIE

Новичок
id- первичный
user_id индекс
word полнотекстовый
type индекс
Вот так расставил

Больше нет вариантов ускорить? Только если убрать HAVING COUNT и перебирать массивы на php?
 

Gas

может по одной?
правильно говорит Вурдалак, в having её ставить смысла нет, странно что это вообще у тебя работает
дату в where, и индекс на date добавь
 

NikSestrin

Новичок
FRIE написал(а):
id- первичный
user_id индекс
word полнотекстовый
type индекс
Вот так расставил

Больше нет вариантов ускорить? Только если убрать HAVING COUNT и перебирать массивы на php?
Нажмите для раскрытия...

Если Вы оставите все Ваши индексы, то в дальнейшем, если количество записей в таблице words увеличится хотябы вдвое, Ваш хостер "снесет" Вам голову.
Нельзя же так бездумно играться индексами.
Вам нужно создать индексы только по тем полям, которые участвуют в запросах в секциях фильтрации (или поиска) и сортировки. В вашем случае Вам нужны будут индексы по полям user_id и date.
Во-первых Ваш запрос построен немного неверно. Нужно вынести фильтрацию по дате из подзапроса (отмечено красным). По полю даты построить инкрементный индекс. Ведь это поле участвует в фильтрации данных, а в дальнейшем и в сортировке.

SELECT `id`,`user_id`,`word`,`type`,`date` FROM `words` WHERE `user_id` IN (SELECT `user_id` FROM `words` GROUP BY `user_id` HAVING COUNT(*) > 1) and (`date` >= $from and `date` <= $to) ORDER BY `user_id`,`date`");

Ну индекс по полю id - это само собой разумеется, ведь это ключевое поле.
Индексы по полям word и type НЕ НУЖНЫ (в контексте данного запроса)
Индекс по полю user_id нужен (отмечено синим), но здесь оптимизатор запросов PHP будет его использовать только для сортировки в секции ORDER BY. Именно для фильтрации в секции WHERE этот индекс использоваться оптимизатором не будет (скорости в выборке не добавится).
Тот факт что у Вас якобы возросло быстродействие до 13 сек, то это только за счет использования индекса по полю user_id в секции сортировки. Если создадите индек по полю даты, то вопрос о быстродействии отпадет раз и навсегда. Все должно просто летать.Тем более, что 8000 записей в таблице, это просто мизер.
 

Gas

может по одной?
NikSestrin
user_id не только для сортировки, ещё для "WHERE `user_id` IN ()"
Другое дело что mysql запросы "in (select ...)" может странно исполнять, но для начала нужно разобраться со всем остальным.
Куда тыкнуть "`date` >= $from and `date` <= $to" это тоже вопрос, во where подзапрос'а, во внешний where или и туда и туда, так как в итоге это 3 разных запроса, которые могут возращать разный результат. Это только автор должен знать что именно ему нужно.
 
Последнее редактирование:
FRIE

FRIE

Новичок
NikSestrin написал(а):
.
Нажмите для раскрытия...
Ваш запрос к mysql работает 9 секунд, прирост хороший. По индексам спасибо за классное разьяснение! Но оно почти прироста не дало, зато удалил лишние.
А теперь запрос работает меньше секунды и вот в чём прикол!
Код: 
$result = mysql_query("SELECT `id`,`user_id`,`word`,`type`,`date` FROM `words` WHERE
(`date` >= $from and `date` <= $to)  and `user_id`  IN (SELECT `user_id` FROM `words` GROUP
BY `user_id` HAVING COUNT(*) > 1) ORDER BY `user_id`,`date`");
Я сделал сначала выбор даты, а потом уже эти пляски с уникальными значениями =)

Спасибо всем кто помог!
 

Вурдалак

Продвинутый новичок
Помимо «оптимизации» — это два разных запроса, возвращающие в общем случае разные результаты.
 

NikSestrin

Новичок
Gas написал(а):
NikSestrin
user_id не только для сортировки, ещё для "WHERE `user_id` IN ()"
Другое дело что mysql запросы "in (select ...)" может странно исполнять, но для начала нужно разобраться со всем остальным.
Куда тыкнуть "`date` >= $from and `date` <= $to" это тоже вопрос, во where подзапрос'а, во внешний where или и туда и туда, так как в итоге это 3 разных запроса, которые могут возращать разный результат. Это только автор должен знать что именно ему нужно.
Нажмите для раскрытия...
Да нет - в данном случае ТОЛЬКО для сортировки. В конструкции IN (Select ...) подзапрос возвращает набор АБСОЛЮТНО всех значений столбца user_id. Предикат IN приводит к сравнению user_id основного запроса с каждым возвращенным значением набора (подзапроса), пока не будет найдено соответствие. Поэтому во время выполнения подзапроса, фетч его записей производится поочередно и сразу же сравнивается с user_id основного запроса. В этом случае таблица индексов по этому полю оптимизатором просто игнорируется.

Ну а по поводу куда ткнуть поле даты. Так тут и думать нечего. Ведь по сути в каждом из трех запросов производится запрос из одной единственной таблицы. И более наглядней отбор по дате "смотрится" в основном запросе, ИМХО.
И еще, ведь этот запрос используется автором для отбора повторяющихся значений поля user_id в таблице words. Зачем это нужно - только одному ему известно. Ну как по мне, для администрирования данных в базе данных, то можно 13 сек подождать, а если для организации бизнес логики приложения, то тут скорее всего проблема в построении структуры БД и ее нормализации. Это уже другая тема.
 

NikSestrin

Новичок
FRIE написал(а):
Ваш запрос к mysql работает 9 секунд, прирост хороший. По индексам спасибо за классное разьяснение! Но оно почти прироста не дало, зато удалил лишние.
А теперь запрос работает меньше секунды и вот в чём прикол!
Код: 
$result = mysql_query("SELECT `id`,`user_id`,`word`,`type`,`date` FROM `words` WHERE
(`date` >= $from and `date` <= $to)  and `user_id`  IN (SELECT `user_id` FROM `words` GROUP
BY `user_id` HAVING COUNT(*) > 1) ORDER BY `user_id`,`date`");
Я сделал сначала выбор даты, а потом уже эти пляски с уникальными значениями =)

Спасибо всем кто помог!
Нажмите для раскрытия...
Ну так здесь тоже нет "неожиданностей". Я как-то сразу не "вкурил". Смотрите - у Вас есть два индекса - по полям user_id, date.
Если в условиях выборки первым стоит поле user_id (как в самом первом Вашем посте), то в конструкции IN (SELECT...) индекс для отбора записей не используется (см. предыдущий мой пост), а только для сртировки. Вторым стоит поле date. Здесь индекс используется только для отбора одной записи, возвращенной подзапросом. Практически можно сказать, что индекс не используется. Поэтому прироста в скорости нет. Ведь перебираются все 8000 записей.
А вот в случае если в условиях выборки первым стоит поле date, то используется индекс по этому полю. Например отбираются 30 или 50 записей, попадающие в итервал (`date` >= $from and `date` <= $to) и уже потом подзапросом выбираются нужные записи. Ведь есть же разница - перебрать 50 или 8000 записей. Теперь на самом деле уже будет неважно - хоть три миллиона записей. Индекс будет делать свое дело. И еще в запросах ненужные индексы можно и нужно отключать. Ибо это зло.
 

Gas

может по одной?
FRIE

9 сек. это тоже слишком дофига, попробуй создать составной индекс (user_id, date), удалить одиночный user_id:

alter table `words` add index user_date(user_id, date);
alter table drop index user_id; (или как он у тебя называется, тот который только по колонке user_id)

и выполнить запрос:

Код: 
SELECT w2.`id`, w2.`user_id`, w2.`word`, w2.`type`, w2.`date`
FROM (
    SELECT `user_id` FROM `words` GROUP BY `user_id` HAVING COUNT(*) > 1
) AS w1
JOIN `words` AS w2 ON w1.user_id = w2.user_id
WHERE `date` >= $from and `date` <= $to
ORDER BY w2.`user_id`, w2.`date`
 
Последнее редактирование:

Gas

может по одной?
Offtop:

чё-то мне казалось в mysql 5.5 пофиксили тормоза "особенность" WHERE field IN (SELECT field ...)
ан нет

Код: 
SELECT SQL_NO_CACHE t2.user_id
FROM (
   SELECT user_id FROM comments GROUP BY user_id HAVING COUNT(*) > 100
) AS t
JOIN comments AS t2 ON t.user_id=t2.user_id
в mysql 5.1.49 и 5.5.31 выполнется 0.02 сек

а по смыслу точно такой же:
Код: 
SELECT SQL_NO_CACHE user_id
FROM comments
WHERE user_id IN (SELECT user_id FROM comments GROUP BY user_id HAVING COUNT(*) > 100)
выполняется в mysql 5.1.49 - 4 минуты, а на 5.5.31 - 2 минуты.

то-есть похоже так и осталось, что подзапрос выполняется не один раз, а каждый раз для каждой записи из внешней таблицы.
5.6 версии нет что там потестить.

FRIE
так что попробуй мой последний запрос, можешь даже без добавления составного индекса, а оставь индексы как есть, очень возможно увидишь неслабый прирост скорости.
 
Последнее редактирование:

NikSestrin

Новичок
Gas написал(а):
Offtop:

чё-то мне казалось в mysql 5.5 пофиксили тормоза "особенность" WHERE field IN (SELECT field ...)
ан нет

Код: 
SELECT SQL_NO_CACHE t2.user_id
FROM (SELECT user_id FROM comments GROUP BY user_id HAVING COUNT(*) > 100) AS t
JOIN comments AS t2 ON t.user_id=t2.user_id
в mysql 5.1.49 и 5.5.31 выполнется 0.02 сек

а по смыслу точно такой же:
Код: 
SELECT SQL_NO_CACHE user_id
FROM comments
WHERE user_id IN (SELECT user_id FROM comments GROUP BY user_id HAVING COUNT(*) > 100)
выполняется в mysql 5.1.49 - 4 минуты, а на 5.5.31 - 2 минуты.

то-есть похоже так и осталось, что подзапрос выполняется не один раз, а каждый раз для каждой записи из внешней таблицы
Нажмите для раскрытия...
Ну так а я о чем...
И версии PHP тут ни причем. Просто индексы в конструкциях IN (Select...) не работают. Это просто нелогично. И это не только в PHP.
 

Gas

может по одной?
NikSestrin
надеюсь ты описАлся, и хотел сказать не "версии PHP", а версии Mysql.

Просто индексы в конструкциях IN (Select...) не работают. Это просто нелогично.
Нажмите для раскрытия...
то-есть по-твоему использовать индексы в запросе "field IN (1,2,3)" логично, а в запросе "field IN (select )" не логично, интересная логика.
И конечно это работает нормально в других БД.
Но mysql не выполняет сначала подзапрос чтоб его результат подставить в IN, что как раз и является логичным и ожидаемым поведением.
Писали что в какой-то версии они это пофиксили, казалось в 5.5, но на практике не подтверждается.

https://blog.mozilla.org/it/2013/01/29/in-subqueries-in-mysql-5-6-are-optimized-away/
в 5.6 таки это починили.

В интернетах помимо джойна, пишут ещё про workaround о котором раньше не знал:

Код: 
SELECT SQL_NO_CACHE user_id
FROM comments
WHERE user_id IN (
   SELECT * FROM (
       SELECT user_id FROM comments GROUP BY user_id HAVING COUNT(*) > 100
   ) AS t
);
работает 0.15 сек, что лучше 4 минут у запроса:

Код: 
SELECT SQL_NO_CACHE user_id
FROM comments
WHERE user_id IN (
   SELECT user_id FROM comments GROUP BY user_id HAVING COUNT(*) > 100
);
но всё равно вариант с джойном и вложенным запросом намного быстрее - 0.02 сек
 
Последнее редактирование:
Войдите или зарегистрируйтесь для ответа.
Сверху