SQLinfo.ru - Все о MySQL

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 20.10.2016 17:02:00

x-miller-x
Завсегдатай
Зарегистрирован: 22.04.2012
Сообщений: 58

как оптимизировать запрос, если в таблицах по 100 000+ записей

как оптимизировать этот запрос когда в обоих таблицах по 100 000+ записей

select count(*) FROM user WHERE `user`.`email` !=""  AND NOT EXISTS (SELECT 1 FROM `notify_user` WHERE (`notify_user`.user_id = `user`.`id` OR `notify_user`.email = `user`.`email`) AND (`notify_user`.`event`=103 OR `notify_user`.`event`=105)) AND user.email != '' GROUP BY user.email ORDER BY user.id ASC

сейчас выполняется по часу или даже больше

Неактивен

 

#2 20.10.2016 17:10:25

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: как оптимизировать запрос, если в таблицах по 100 000+ записей

покажите структуру таблиц (show create table ..), план запроса и версию

Неактивен

 

#3 21.10.2016 07:53:41

x-miller-x
Завсегдатай
Зарегистрирован: 22.04.2012
Сообщений: 58

Re: как оптимизировать запрос, если в таблицах по 100 000+ записей

CREATE TABLE `user` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Идентификатор',
 `email` varchar(128) DEFAULT NULL,
 `last_login` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Штамп последнего логина',
 `banned` int(1) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `email` (`email`),
 KEY `last_login` (`last_login`),
 KEY `banned` (`banned`)
) ENGINE=InnoDB AUTO_INCREMENT=127446 DEFAULT CHARSET=utf8 COMMENT='Пользователи'

CREATE TABLE `notify_user` (
 `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'Идентификатор',
 `user_id` int(11) NOT NULL COMMENT 'Пользователь',
 `event` int(11) NOT NULL COMMENT 'Событие',
 `cnt_notify` int(11) DEFAULT '0' COMMENT 'Количество уведомлений',
 `last_notify` timestamp NULL DEFAULT NULL COMMENT 'Последнее уведомление',
 `email` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`),
 KEY `event` (`event`),
 KEY `cnt_notify` (`cnt_notify`),
 KEY `last_notify` (`last_notify`),
 KEY `user_id` (`user_id`)
 KEY `email` (`email`),
) ENGINE=InnoDB AUTO_INCREMENT=54595 DEFAULT CHARSET=utf8 COMMENT='Теги шаблонов письма'
 

Неактивен

 

#4 21.10.2016 10:36:12

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: как оптимизировать запрос, если в таблицах по 100 000+ записей

1. user.email != ''  дважды используется

2. GROUP BY user.email  -- зачем? в данном запросе смысла не имеет.

3. AND NOT EXISTS (SELECT 1 FROM `notify_user` WHERE (`notify_user`.user_id = `user`.`id` OR `notify_user`.email = `user`.`email`) AND (`notify_user`.`event`=103 OR `notify_user`.`event`=105))
замените на
AND NOT EXISTS (SELECT 1 FROM `notify_user` WHERE `notify_user`.user_id = `user`.`id` AND (`notify_user`.`event`=103 OR `notify_user`.`event`=105)
and
AND NOT EXISTS (SELECT 1 FROM `notify_user` WHERE `notify_user`.email = `user`.`email` AND (`notify_user`.`event`=103 OR `notify_user`.`event`=105)

4. (`event`, user_id) и (`event`, email)

5. ответить на остальные вопросы

6. что вообще вы отите получить этим запросом?

Неактивен

 

#5 21.10.2016 11:40:13

x-miller-x
Завсегдатай
Зарегистрирован: 22.04.2012
Сообщений: 58

Re: как оптимизировать запрос, если в таблицах по 100 000+ записей

1. убрал
2. получилось так что в таблице юзеров появились несколько юзеров в одинаковыми email, чтобы повторно не отправлять письмо одному и тому же email группирую по email
3. заменил
4. Не совсем понял что надо делать
6. В notify_user хранятся пользователи, которым была рассылка, теперь надо выбрать только тех юзеров, которым надо отправить с исключением юзеров, которые отмечены в notify_user

Неактивен

 

#6 21.10.2016 12:00:22

x-miller-x
Завсегдатай
Зарегистрирован: 22.04.2012
Сообщений: 58

Re: как оптимизировать запрос, если в таблицах по 100 000+ записей

select count(*) FROM user WHERE `user`.`email` !=""
AND NOT EXISTS (SELECT 1 FROM `notify_user` WHERE `notify_user`.`user_id` = `user`.`id` AND (`notify_user`.`event`=103 OR `notify_user`.`event`=105))
AND NOT EXISTS (SELECT 1 FROM `notify_user` WHERE `notify_user`.email = `user`.`email` AND (`notify_user`.`event`=103 OR `notify_user`.`event`=105))
GROUP BY user.email ORDER BY user.id ASC
этот запрос работает, только выводит все записи с единичками а не сумму, то есть выводит 48000 записей единичек

Неактивен

 

#7 21.10.2016 12:22:30

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: как оптимизировать запрос, если в таблицах по 100 000+ записей

select count(*) .. group by user.email -- даст кол-во записей в группе для каждого user.email
т.е. вы получите набор каких-то цифр, который не понятно как в дальнейшем использовать.

приведите пример тестовых данных (insert into ..)  и какой результат вы хотите получить на этих данных.

Неактивен

 

#8 21.10.2016 14:49:23

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: как оптимизировать запрос, если в таблицах по 100 000+ записей

x-miller-x написал:

4. Не совсем понял что надо делать

надо добавить составные индексы
см FAQ №5

Неактивен

 

#9 21.10.2016 14:58:09

x-miller-x
Завсегдатай
Зарегистрирован: 22.04.2012
Сообщений: 58

Re: как оптимизировать запрос, если в таблицах по 100 000+ записей

Таблица user
http://dl2.joxi.net/drive/2016/10/21/0006/1977/436153/53/78df13b891.jpg
Таблице notify_user
http://dl2.joxi.net/drive/2016/10/21/0006/1977/436153/53/2fdfb436d6.jpg

Надо сделать выборку из user. которых нет в таблице notify_user по user_id и по email, для примера добавил одинаковые email, их надо исключить

Неактивен

 

#10 21.10.2016 15:26:27

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: как оптимизировать запрос, если в таблицах по 100 000+ записей

и что должно получиться в итоговой выборке?

Неактивен

 

#11 21.10.2016 16:22:16

x-miller-x
Завсегдатай
Зарегистрирован: 22.04.2012
Сообщений: 58

Re: как оптимизировать запрос, если в таблицах по 100 000+ записей

о работает

select id, email FROM user WHERE `user`.`email` !="" AND user.last_login <= '2016-08-01'
AND NOT EXISTS (SELECT 1 FROM `notify_user` WHERE `notify_user`.`user_id` = `user`.`id` AND (`notify_user`.`event`=103 OR `notify_user`.`event`=105))
AND NOT EXISTS (SELECT 1 FROM `notify_user` WHERE `notify_user`.email = `user`.`email` AND (`notify_user`.`event`=103 OR `notify_user`.`event`=105))
GROUP BY user.email ORDER BY user.id ASC


А как count(*) сделать чтобы количество выводилось?

Неактивен

 

#12 21.10.2016 19:03:41

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: как оптимизировать запрос, если в таблицах по 100 000+ записей

предположу, что
select id, email, count(*) FROM ...
но вы бы привели пример желаемого результата на тестовых данных, чтобы не приходилось гадать.

Неактивен

 

#13 22.10.2016 07:44:27

x-miller-x
Завсегдатай
Зарегистрирован: 22.04.2012
Сообщений: 58

Re: как оптимизировать запрос, если в таблицах по 100 000+ записей

http://dl1.joxi.net/drive/2016/10/22/0006/1977/436153/53/52becb8570.jpg вот результат выборки

а когда делаю select count(*)
http://dl1.joxi.net/drive/2016/10/22/0006/1977/436153/53/d80b1320ae.jpg
а надо, чтобы выводил 5, то есть сумму

Неактивен

 

#14 22.10.2016 13:34:46

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: как оптимизировать запрос, если в таблицах по 100 000+ записей

select count(*) from
(select id FROM user WHERE `user`.`email` !="" AND user.last_login <= '2016-08-01'
AND NOT EXISTS (SELECT 1 FROM `notify_user` WHERE `notify_user`.`user_id` = `user`.`id` AND (`notify_user`.`event`=103 OR `notify_user`.`event`=105))
AND NOT EXISTS (SELECT 1 FROM `notify_user` WHERE `notify_user`.email = `user`.`email` AND (`notify_user`.`event`=103 OR `notify_user`.`event`=105))
GROUP BY user.email) t;


Так?

Неактивен

 

#15 23.10.2016 19:28:27

x-miller-x
Завсегдатай
Зарегистрирован: 22.04.2012
Сообщений: 58

Re: как оптимизировать запрос, если в таблицах по 100 000+ записей

ага спасибо)

Неактивен

 

#16 24.11.2016 15:06:41

x-miller-x
Завсегдатай
Зарегистрирован: 22.04.2012
Сообщений: 58

Re: как оптимизировать запрос, если в таблицах по 100 000+ записей


 SELECT t2.product_id AS `id` , count( t2.product_id ) AS `count`
FROM `product_to_tag` AS t1
LEFT JOIN `product_to_tag` AS t2 ON t1.tag_id = t2.tag_id
WHERE
t1.tag_id NOT
IN (

SELECT id
FROM tag_stop
)
 AND t1.product_id =12
AND t1.product_id <> t2.product_id
GROUP BY t2.product_id
HAVING `count` >=3
ORDER BY `count` DESC
 
вот еще можно как то оптимизировать этот запрос занимает в среднем 80 секунд(

Отредактированно x-miller-x (24.11.2016 16:43:40)

Неактивен

 

#17 24.11.2016 15:12:25

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: как оптимизировать запрос, если в таблицах по 100 000+ записей

Неактивен

 

#18 24.11.2016 16:19:07

x-miller-x
Завсегдатай
Зарегистрирован: 22.04.2012
Сообщений: 58

Re: как оптимизировать запрос, если в таблицах по 100 000+ записей

http://dl2.joxi.net/drive/2016/11/24/0006/1977/436153/53/c26865f393.jpg
http://dl1.joxi.net/drive/2016/11/24/0006/1977/436153/53/3a98a65d5a.jpg

Отредактированно x-miller-x (24.11.2016 16:43:11)

Неактивен

 

#19 24.11.2016 16:30:24

x-miller-x
Завсегдатай
Зарегистрирован: 22.04.2012
Сообщений: 58

Re: как оптимизировать запрос, если в таблицах по 100 000+ записей

Если даже убрать условие

t1.tag_id NOT
IN (

SELECT id
FROM tag_stop
)


сократится всего на несколько секунд,

Отредактированно x-miller-x (24.11.2016 16:43:25)

Неактивен

 

#20 24.11.2016 16:53:02

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: как оптимизировать запрос, если в таблицах по 100 000+ записей

как минимум нужен индекс на tag_id

p.s. лучше отвечать на все задаваемые вопросы

Неактивен

 

#21 24.11.2016 17:06:59

x-miller-x
Завсегдатай
Зарегистрирован: 22.04.2012
Сообщений: 58

Re: как оптимизировать запрос, если в таблицах по 100 000+ записей

vasya написал:

как минимум нужен индекс на tag_id

p.s. лучше отвечать на все задаваемые вопросы

индекс добавил

http://dl2.joxi.net/drive/2016/11/24/0006/1977/436153/53/f11ebe5816.jpg

Отредактированно x-miller-x (24.11.2016 17:22:49)

Неактивен

 

#22 24.11.2016 17:25:03

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 5827

Re: как оптимизировать запрос, если в таблицах по 100 000+ записей

время изменилось?

Неактивен

 

#23 24.11.2016 17:51:47

x-miller-x
Завсегдатай
Зарегистрирован: 22.04.2012
Сообщений: 58

Re: как оптимизировать запрос, если в таблицах по 100 000+ записей

так вроде все быстро работать стал, еще парочка запросов имеется) если не разберусь с ними напишу)

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson