Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Как оптимизировать запрос WHERE IN ? / 12 сообщений из 12, страница 1 из 1
02.02.2014, 00:47:23
    #38546153
gogoduck
gogoduck
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать запрос WHERE IN ?
Здравствуйте уважаемые гуру, помогите с запросом:

Код: sql
1.
2.
3.
SELECT t1.cl_id, t2.cl_tp FROM criteria t1 LEFT JOIN criteria_int t2 ON t1.idx=t2.idx
WHERE t1.idx IN (21,32,543,345,544344,223,2800000....... 50 знаков)
ORDER BY FIELD (t1.idx,21,32,543,345,544344,223,2800000....... 50 знаков)



Выполняется 0.3-3 сек., в зависимости от значений.
Код: sql
1.
2.
3.
4.
5.
6.
+----+-------------+-------+-------+---------------+----------+---------+----------------------+------+------------------------------------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref                  | rows | Extra                                    |
+----+-------------+-------+-------+---------------+----------+---------+----------------------+------+------------------------------------------+
|  1 | SIMPLE      | t1    | range | idx           | idx      | 4       | NULL                 |   50 | Using where; Using index; Using filesort |
|  1 | SIMPLE      | t2    | ref   | idx           | idx      | 4       | db_test.t1.idx       |    1 | Using index                              |
+----+-------------+-------+-------+---------------+----------+---------+----------------------+------+------------------------------------------+



Если убрать ORDER BY FIELD скорости прибавляется примерно 40%, исчезает Using filesort, как правильно сделать запрос? Нужна сортировка в таком порядке, в каком пришли idx.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.02.2014, 00:59:48
    #38546159
javajdbc
javajdbc
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать запрос WHERE IN ?
gogoduck,

какая версия сервера? вроде до 5.5
там было неоптимизированый "IN"

вообше время какоето страно большое
(или у вас полтрилона записей?)

попробуйте так:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
SELECT t101.cl_id, t2.cl_tp 
FROM
(
SELECT t1.idx,  t1.cl_id
FROM criteria t1 
WHERE t1.idx IN (21,32,543,345,544344,223,2800000....... 50 знаков)
) t101
LEFT JOIN 
criteria_int t2 ON t101.idx=t2.idx
ORDER BY FIELD (t101.idx,21,32,543,345,544344,223,2800000....... 50 знаков)



может ФИЕЛД такой медленый оператор?
вообшето можно на CASE заменить, но некузяво писать
50 иф-елсо-в
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.02.2014, 01:03:45
    #38546161
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать запрос WHERE IN ?
Сортировка по выражению - это filesort без вариантов. Не лечится.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.02.2014, 01:08:47
    #38546166
javajdbc
javajdbc
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать запрос WHERE IN ?
AkinaСортировка по выражению - это filesort без вариантов. Не лечится.

ну и лечить не надо, это же не обязательно на диске.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.02.2014, 01:11:12
    #38546167
gogoduck
gogoduck
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать запрос WHERE IN ?
javajdbc,

Версия
Код: sql
1.
Server version: 5.1.69 Source distribution



Попробую обновиться завтра. В каждой таблице по 3 млн записей
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.02.2014, 01:25:09
    #38546181
javajdbc
javajdbc
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать запрос WHERE IN ?
gogoduck,

да 5.5. или текушая 5.6 будет лучше.
Если это тесты, то лучше сделать чистую инсталяцию
с переносом таблиц дампом, а не апгрейд.
Там меняются служебные/статистические таблицы,
меняются опции в ини файлах, что-то еше.... ничего сверхсложного
но пришлось повозится недавно (5.1>5.6 апдейт)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.02.2014, 14:49:13
    #38546350
gogoduck
gogoduck
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать запрос WHERE IN ?
javajdbc,

Спасибо за инфу, обновился нормально до:

Код: sql
1.
Server version: 5.6.16 MySQL Community Server (GPL)



Запрос начал работать ещё медленнее. Даже на простом WHERE IN, без JOIN и ORDER BY FIELD выбор 50 рандомных idx по индексу проходит в 0.40 сек.(!).

В чем может быть проблема, может, кто даст дельный совет? Я грешу на виртуалку (контейнер VMWare 1 CPU, 768RAM). Тестовый скрипт, который выбирает 50 случайных записей по 100 раз (в цикле) завешивает все.

Команда top показывает %wa 70-90%. Понятное дело, что не хватает памяти и возрастает нагрузка на диск, но не до такой же степени. В RAM таблицу хранить тоже возможности нет. Что может помочь? Память и диск SSD конечно могу добавить, но меня пугает, что даже на такой конфигурации все завешивается простой выборкой.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.02.2014, 15:33:44
    #38546377
javajdbc
javajdbc
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать запрос WHERE IN ?
gogoduck,

Варианты дальнейших исследований

1. сделайте копию на своей локальной машине
и посмотрите скорость

2. Какая скорость поиска одной записи (вместро 50 ) ?
(совсем без никаких жоинтов.)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.02.2014, 15:41:19
    #38546380
gogoduck
gogoduck
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать запрос WHERE IN ?
javajdbc,

1 запись стабильно 0.02 сек., это нормально для такой конфигурации? В таблице всего 3 млн записей

Попробую на более мощную машину перенести это дело.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.02.2014, 22:33:46
    #38546619
Akina
Akina
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать запрос WHERE IN ?
Да слей ты уже свои "50 знаков" во временную memory-таблицу с полями idx и SortOrder...
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
02.02.2014, 23:17:23
    #38546654
gogoduck
gogoduck
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать запрос WHERE IN ?
Притормаживает именно выборка по where in, сортировка ещё более менее..

Сменил таблички на myisam, скорость увеличилась ещё в 2 раза. Считаю что на такой конфигурации это максимум, тем более query cache работает отлично, выборка после "соточки" прогонов увеличивается с каждым разом )

Просто любопытно, к примеру, есть база:

CREATE TABLE IF NOT EXISTS `test_db` (
`id` INT NULL AUTO_INCREMENT,
`slug` INT UNSIGNED NOT NULL DEFAULT 0,
PRIMARY KEY (`id`))
ENGINE = myisam

Заполнено под 100 млн. Какая скорость по random WHERE id LIMIT 1 должна быть на хорошем железе? 0.00? Это фантастика? База на диске.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.02.2014, 09:28:48
    #38546801
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Как оптимизировать запрос WHERE IN ?
javajdbcкакая версия сервера? вроде до 5.5
там было неоптимизированый "IN"Это было про IN (SELECT ...), а тут список констант.

Имхо, в табличку нужно класть и делать ее ведущей в запросе.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Как оптимизировать запрос WHERE IN ? / 12 сообщений из 12, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 790ms
Закрыть
start [/forum/search_topic.php?author=vebber&author_mode=last_posts&do_search=1]:
 0ms
get settings:
 9ms
get forum list:
 20ms
get settings:
 11ms
get forum list:
 16ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 28ms
get topic data:
 13ms
get forum data:
 3ms
get page messages:
 47ms
get tp. blocked users:
 1ms
others: 636ms
total:  790ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]