Модераторы: skyboy, SamDark
  

Поиск:

Ответ в темуСоздание новой темы Создание опроса
> Оптимизировать multiple IN 
:(
    Опции темы
Fubu_By
  Дата 26.7.2018, 13:08 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Опытный
**


Профиль
Группа: Участник
Сообщений: 290
Регистрация: 14.2.2007

Репутация: нет
Всего: 0



Есть такой запрос, который выполняется 1.3 секунды:
Код

SELECT * FROM table WHERE (origin_city_id,destination_city_id) IN (("4081","25"),("4081","50"),("4081","113"),("4081","182"),("4081","214"),("4081","214"),("4081","341"),("4081","347"),
("4081","472"),("4081","472"),("4081","472"),("4081","472"),("4081","583"),("4081","595"),("4081","597"),("4081","616"),("4081","638"),("4081","694"),("4081","696"),("4081","696"),
("4081","696"),("4081","718"),("4081","718"),("4081","718"),("4081","768"),("4081","770"),("4081","770"),("4081","770"),("4081","775"),("4081","779"),("4081","779"),("4081","779"),
("4081","814"),("4081","825"),("4081","877"),("4081","877"),("4081","877"),("4081","877"),("4081","983"),("4081","983"),("4081","985"),("4081","1035"),("4081","1058"),("4081","1058"),
("4081","1081"),("4081","1115"),("4081","1115"),("4081","1115"),("4081","1115"),("4081","1115"),("4081","1224"),("4081","1262"),("4081","1262"),("4081","1313"),("4081","1313"),
("4081","1313"),("4081","1319"),("4081","1345"),("4081","1345"),("4081","1462"),("4081","1462"),("4081","1557"),("4081","1574"),("4081","1574"),("4081","1574"),("4081","1607"),
("4081","1607"),("4081","1840"),("4081","1923"),("4081","1940"),("4081","1949"),("4081","2164"),("4081","2164"),("4081","2164"),("4081","2170"),("4081","2238"),("4081","2258"),
("4081","2258"),("4081","2258"),("4081","2283"),("4081","2283"),("4081","2305"),("4081","2305"),("4081","2309"),("4081","2309"),("4081","2309"),("4081","2380"),("4081","2382"),
("4081","2382"),("4081","2382"),("4081","2794"),("4081","2807"),("4081","3016"),("4081","3061"),("4081","3063"),("4081","3063"),("4081","3098"),("4081","3098"),("4081","3098"),
("4081","3165"),("4081","3347"),("4081","3366"),("4081","3366"),("4081","3366"),("4081","3445"),("4081","3448"),("4081","3448"),("4081","3448"),("4081","3538"),("4081","3571")
,("4081","3571"),("4081","3571"),("4081","3576"),("4081","3576"),("4081","3654"),("4081","3654"),("4081","3774"),("4081","3787"),("4081","3787"),("4081","3787"),("4081","3787"),
("4081","3796"),("4081","3796"),("4081","3807"),("4081","3843"),("4081","3873"),("4081","3884"),("4081","3884"),("4081","3884"),("4081","3884"),("4081","3898"),("4081","3951"),
("4081","3951"),("4081","3951"),("4081","3951"),("4081","3951"),("4081","3951"),("4081","3958"),("4081","3958"),("4081","3958"),("4081","3958"),("4081","4006"),("4081","4006"),
("4081","4006"),("4081","4051"),("4081","4051"),("4081","4051"),("4081","4094"),("4081","4146"),("4081","4191"),("4081","4191"),("4081","4212"),("4081","4326"),("4081","4326"),
("4081","4326"),("4081","4342"),("4081","4342"),("4081","4342"),("4081","4503"),("4081","4539"),("4081","4539"),("4081","4539"),("4081","4539"),("4081","4539"),("4081","4539"),
("4081","4641"),("4081","4641"),("4081","4694"),("4081","4694"),("4081","4703"),("4081","4805"),("4081","4805"),("4081","4805"),("4081","4805"),("4081","4991"),("4081","5005"),
("4081","5005"),("4081","5067"),("4081","5086"),("4081","5127"),("4081","5127"),("4081","5132"),("4081","5136"),("4081","5136"),("4081","5213"),("4081","5227"),("4081","5274"),
("4081","5301"),("4081","5321"),("4081","5321"),("4081","5321"),("4081","5321"),("4081","5321"),("4081","5377"),("4081","5377"),("4081","5377"),("4081","5471"),("4081","5471"),
("4081","5471"),("4081","5471"),("4081","5525"),("4081","5531"),("4081","5572"),("4081","5572"),("4081","5572"),("4081","5572"),("4081","5582"),("4081","5610"),("4081","5610"),
("4081","5610"),("4081","5632"),("4081","5632"),("4081","5632"),("4081","5856"),("4081","5907"),("4081","5907"),("4081","5992"),("4081","5992"),("4081","6140"),("4081","6142"),
("4081","6142"),("4081","6170"),("4081","6231"),("4081","6231"),("4081","6231"),("4081","6236"),("4081","6283"),("4081","6372"),("4081","6372"),("4081","6497"),("4081","6497"),
("4081","6497"),("4081","6497"),("4081","6543"),("4081","6543"),("4081","6579"),("4081","6623"),("4081","6623"),("4081","6623"),("4081","6648"),("4081","6648"),("4081","6866"),
("4081","6906"),("4081","6927"),("4081","6928"),("4081","6929"),("4081","6929"),("4081","6950"),("4081","6950"),("4081","7046"),("4081","7241"),("4081","7241"),("4081","7241"),
("4081","7344"),("4081","7344"),("4081","7344"),("4081","7479"),("4081","7479"),("4081","7487"),("4081","7487"),("4081","8315"),("4081","8324"),("4081","8332"),("4081","8361"),
("4081","8361"),("4081","8381"),("4081","8435"),("4081","8488"),("4081","8522"),("4081","8522"),("4081","8522"),("4081","8562"),("4081","8562"),("4081","8575"),("4081","8575"),
("4081","8605"),("4081","8605"),("4081","8633"),("4081","8673"),("4081","8756"),("4081","8756"),("4081","8756"),("4081","8773"),("4081","8773"),("4081","8773"),("4081","8814"),
("4081","8821"),("4081","8833"),("4081","8861"),("4081","8893"),("4081","8893"),("4081","8893"),("4081","9118"),("4081","9221"),("4081","9221"),("4081","9242"),("4081","9242"),
("4081","9244"),("4081","9244"),("4081","9245"),("4081","9255"),("4081","9260"),("4081","9266"),("4081","9266"),("4081","9271"),("4081","9286"),("4081","9306"))

Индекс по origin_city_id+destination_city_id построен. Таблица не большая, 490.000 записей, innodb...

Как лучше переделать запрос, чтобы выполнялся на порядок хотя бы быстрее?

Это сообщение отредактировал(а) Fubu_By - 26.7.2018, 13:13


--------------------
PM MAIL WWW   Вверх
Akina
Дата 26.7.2018, 14:39 (ссылка) | (нет голосов) Загрузка ... Загрузка ... Быстрая цитата Цитата


Советчик
****


Профиль
Группа: Модератор
Сообщений: 20360
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 104
Всего: 447



1) Применительно к конкретно этому запросу - преобразуй его в 
Код

WHERE origin_city_id = 4081 AND destination_city_id IN (список)


2) Если типы полей origin_city_id и destination_city_id числовые - убирай нафиг кавычки от значений. 

3) Глобально - создай временную таблицу с Engine=Memory и индексом, заливай в неё пары значений, а потом делай SELECT из неё и основной таблицы.


--------------------
 О(б)суждение моих действий - в соответствующей теме, пожалуйста. Или в РМ. И высшая инстанция - Администрация форума.

PM MAIL WWW ICQ Jabber   Вверх
Google
  Дата 13.11.2018, 01:08 (ссылка)  





  Вверх
  
Ответ в темуСоздание новой темы Создание опроса
1 Пользователей читают эту тему (1 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема | MySQL | Следующая тема »


 




[ Время генерации скрипта: 0.0782 ]   [ Использовано запросов: 21 ]   [ GZIP включён ]


Реклама на сайте     Информационное спонсорство

 
По вопросам размещения рекламы пишите на vladimir(sobaka)vingrad.ru
Отказ от ответственности     Powered by Invision Power Board(R) 1.3 © 2003  IPS, Inc.