ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / долго выполняется запрос на not exist (или left join where field is null)
14 сообщений из 14, страница 1 из 1
долго выполняется запрос на not exist (или left join where field is null)
    #38320798
Lisichkin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lisichkin
Участник
Добрый день!
Помогите с запросом на not exist (или left join where field is null - пробовал и так все без толку..)
На MSSQL данный запрос выполняется в лет, а на MySQL – секунд 10 (это не было бы так критично если доступ к базе не был через браузер и phpMyAdmin…который отваливается по тайм ауту.

Итак:
Имеются таблицы (объемы смехотворные):
1624 row
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
CREATE TABLE operators (
  _id int(11) NOT NULL AUTO_INCREMENT,
  MCC varchar(3) NOT NULL,
  MNC varchar(6) NOT NULL,
  IMSI varchar(8) NOT NULL,
  Name varchar(255) NOT NULL,
  .....
  PRIMARY KEY (_id),
  INDEX Operators_MCC (MCC),
  INDEX Operators_MNC (MCC, MNC),
  INDEX Operators_IMSI (IMSI),
)


232 row
Код: sql
1.
2.
3.
4.
5.
6.
7.
CREATE TABLE countries (
  _id int(11) NOT NULL AUTO_INCREMENT,
  MCC varchar(3) NOT NULL,
  Name varchar(60) NOT NULL,
  PRIMARY KEY (_id),
  UNIQUE INDEX Countries_MCC (MCC)
)



Порядка 3-4 тысяч записей
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
CREATE TABLE updatelog (
  _id int(11) NOT NULL AUTO_INCREMENT,
  MNC varchar(8) NOT NULL,
  Name text NOT NULL,
  count int(11) NOT NULL,
  lastUpdate datetime DEFAULT NULL,
  PRIMARY KEY (_id),
  INDEX UpdateLog_MNC (MNC),
)


Запрос
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
SELECT l.MNC, l.NAME, c.NAME, count(*), sum(l.count), max(l.lastUpdate)
FROM `UpdateLog` l
INNER JOIN `Countries` c ON c.MCC = LEFT(l.MNC, 3)
WHERE l.MNC <> ''
  AND l.MNC not in (select CONCAT( MCC, MNC ) from `Operators`)
GROUP BY l.MNC, c.NAME, l.NAME
HAVING DATEDIFF(CURDATE(), max(l.lastUpdate))<=30
ORDER BY l.MNC, 
  count(*), sum(count), max(l.lastUpdate)



Выполняется не позволительно долго :(

EXPLAIN Выдает следующую картину:
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY l ALL UpdateLog_MNC NULL NULL NULL 15715 Using where; Using temporary; Using filesort
1 PRIMARY c eq_ref Countries_MCC Countries_MCC 11 func 1 Using where
2 DEPENDENT SUBQUERY Operators index NULL Operators_MNC 31 NULL 1660 Using where; Using index

Помогите исправить запрос.
Спасибо.
...
Рейтинг: 0 / 0
04.07.2013, 16:11:34
| Ответить | Цитировать | Написать  
долго выполняется запрос на not exist (или left join where field is null)
    #38321892
Lisichkin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lisichkin
Участник
Да видимо, CONCAT( MCC, MNC ) from `Operators`, - для MySQL "убийственная" операция, приводящая к не использование индекса INDEX Operators_MNC (MCC, MNC) :((

Добавляется поле MCC_MNC в таблице `Operators` и строится индекс по нему. Меняем запрос на:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
SELECT l.MNC, l.NAME, c.NAME, count(*), sum(l.count), max(l.lastUpdate)
FROM `UpdateLog` l
INNER JOIN `Countries` c ON c.MCC = LEFT(l.MNC, 3)
WHERE l.MNC <> ''
  AND l.MNC not in (select MNC_MCC from `Operators`)
GROUP BY l.MNC, c.NAME, l.NAME
HAVING DATEDIFF(CURDATE(), max(l.lastUpdate))<=30
ORDER BY l.MNC, 
  count(*), sum(count), max(l.lastUpdate)



Время исполнения - 0,167c. Увеличение быстродействия в 42 раза.
Печально что MySQL при сложении строк перестает использовать индекс...
...
Рейтинг: 0 / 0
05.07.2013, 14:04:41
| Ответить | Цитировать | Написать  
долго выполняется запрос на not exist (или left join where field is null)
    #38321912
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
Lisichkin,

Если версия MySQL ниже, чем 5.6, то избавьтесь от конструкции in (select ...) вообще.
...
Рейтинг: 0 / 0
05.07.2013, 14:13:54
| Ответить | Цитировать | Написать  
долго выполняется запрос на not exist (или left join where field is null)
    #38321926
Lisichkin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lisichkin
Участник
miksoft
Так я not in (select ..) не от хорошей жизни написал - с начала у меня было left join `Operators` o where o.id is null - мне показаться что эта конструкция работает чуть медленнее..
P.S. Версия MySQL 5.5.28
P.S.S. Я в MySQL не очень - больше в MS SQL - там, уверю, такой проблемы не возникло бы.
...
Рейтинг: 0 / 0
05.07.2013, 14:20:03
| Ответить | Цитировать | Написать  
долго выполняется запрос на not exist (или left join where field is null)
    #38321938
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
LisichkinТак я not in (select ..) не от хорошей жизни написал - с начала у меня было left join `Operators` o where o.id is null - мне показаться что эта конструкция работает чуть медленнее..Возможно, ей чего-то не хватало.
Обычно in (select ..) приводит к тому, что подзапрос в скобках выполняется множество раз вместо одного.

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

Еще вариант - вынести проверку l.MNC из группировки. Зависит от того, какая доля записей отбрасывается по этому условию.
...
Рейтинг: 0 / 0
05.07.2013, 14:27:38
| Ответить | Цитировать | Написать  
долго выполняется запрос на not exist (или left join where field is null)
    #38321985
Lisichkin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lisichkin
Участник
miksoft
Запрос с not in (select ..)
Выполняется ~
0,587
0,154
0,174
План:
id select_type table type possible_keys key key_len ref
1 PRIMARY l ALL UpdateLog_MNC (null) (null) (null)
1 PRIMARY c eq_ref Countries_MCC Countries_MCC 11 func
2 DEPENDENT SUBQUERY Operators index_subquery Operators_MNC_MCC Operators_MNC_MCC 26 func

Запрос с left join
Выполняется за ~
0,320
0,321
План:
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE l ALL UpdateLog_MNC (null) (null) (null) 15752 Using where; Using temporary; Using filesort
1 SIMPLE c eq_ref Countries_MCC Countries_MCC 11 func 1 Using where
1 SIMPLE o ref Operators_MNC_MCC Operators_MNC_MCC 26 u28086s1.l.MNC 1 Using where; Not exists

Смысл запроса в том, что в "большой" массе данных в таблице updatelog (сейчас 16 тысяч записей) - находится очень малое количество (не более 10 записей) для которых нет ссылки по полю l.MNC на o.MNC_MCC - поэтому не нужно выносить проверку l.MNC из группировки.
Данные запроса интерпретируются так: Вывести все записи в логе, для которых нет информации из справочника "Операторы"

P.S. Не знаю как красиво оформить план запроса :((
...
Рейтинг: 0 / 0
05.07.2013, 14:54:31
| Ответить | Цитировать | Написать  
долго выполняется запрос на not exist (или left join where field is null)
    #38321999
Lisichkin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lisichkin
Участник
Да, забыл указать второй запрос:
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
SELECT l.MNC, l.NAME, c.NAME, count(*), sum(l.count), max(l.lastUpdate)
FROM `UpdateLog` l
INNER JOIN `Countries` c ON c.MCC = LEFT(l.MNC, 3)
LEFT JOIN `Operators` o ON l.MNC = o.MNC_MCC
WHERE l.MNC <> ''  
  AND o._id IS NULL
GROUP BY l.MNC, c.NAME, l.NAME
HAVING DATEDIFF(CURDATE(), max(l.lastUpdate))<=30
ORDER BY l.MNC, 
  count(*), sum(count), max(l.lastUpdate)
...
Рейтинг: 0 / 0
05.07.2013, 15:02:44
| Ответить | Цитировать | Написать  
долго выполняется запрос на not exist (или left join where field is null)
    #38322134
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
Lisichkin,

А сколько записей получается в результате запроса?
...
Рейтинг: 0 / 0
05.07.2013, 16:00:16
| Ответить | Цитировать | Написать  
долго выполняется запрос на not exist (или left join where field is null)
    #38322151
Lisichkin
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Lisichkin
Участник
miksoft
Немного напутал:
Без
Код: sql
1.
HAVING DATEDIFF(CURDATE(), max(l.lastUpdate))<=30

- Данные обновлявшиеся только за последний месяц - 44 записи
С HAVING - 7 записей.
...
Рейтинг: 0 / 0
05.07.2013, 16:08:18
| Ответить | Цитировать | Написать  
долго выполняется запрос на not exist (или left join where field is null)
    #38322176
Фотография ScareCrow
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ScareCrow
Участник
авторP.S. Не знаю как красиво оформить план запроса :((
скриншотом
...
Рейтинг: 0 / 0
05.07.2013, 16:20:26
| Ответить | Цитировать | Написать  
долго выполняется запрос на not exist (или left join where field is null)
    #38323403
deblogger
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
deblogger
Участник
Lisichkin,

Если возникла потребность искать неизвестное или неравенство, это верный признак что БД спроектирована в корне неправильно. Что, конечно, может отражать неправильные бизнес-процессы.
...
Рейтинг: 0 / 0
07.07.2013, 22:54:42
| Ответить | Цитировать | Написать  
долго выполняется запрос на not exist (или left join where field is null)
    #38323422
неинтересный бот
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
неинтересный бот
Гость
debloggerLisichkin,

Если возникла потребность искать неизвестное или неравенство, это верный признак что БД спроектирована в корне неправильно. Что, конечно, может отражать неправильные бизнес-процессы.

deblogger - интересный бот, кто-то хорошо прикалывается.
...
Рейтинг: 0 / 0
07.07.2013, 23:13:36
| Ответить | Цитировать | Написать  
долго выполняется запрос на not exist (или left join where field is null)
    #38323440
deblogger
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
deblogger
Участник
неинтересный бот,

SELECT beer.name FROM worldbeers WHERE beer.name != "Zhigulyouvskoe";

Как на SQL "почил в бозе ожидая рекордсета"?
...
Рейтинг: 0 / 0
07.07.2013, 23:32:28
| Ответить | Цитировать | Написать  
долго выполняется запрос на not exist (или left join where field is null)
    #38323554
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
debloggerнеинтересный бот,

SELECT beer.name FROM worldbeers WHERE beer.name != "Zhigulyouvskoe";

Как на SQL "почил в бозе ожидая рекордсета"?

а кто алиас beer ставить будет? Пушкин?
я тут подправил, вернулся ресультат: Гинес.
...
Рейтинг: 0 / 0
08.07.2013, 03:25:44
| Ответить | Цитировать | Написать  
14 сообщений из 14, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / долго выполняется запрос на not exist (или left join where field is null)
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&msg=38321912&tid=1836494]:
 0ms
get settings:
 6ms
get forum list:
 16ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 46ms
get topic data:
 9ms
get forum data:
 2ms
get page messages:
 50ms
get tp. blocked users:
 1ms
others: 198ms
total:  334ms
созд. / загр.: 334ms
x
x
Закрыть


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