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

x-miller-x · 20.10.2016 17:02:00

как оптимизировать этот запрос когда в обоих таблицах по 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

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

vasya · 20.10.2016 17:10:25

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

x-miller-x · 21.10.2016 07:53:41

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='Теги шаблонов письма'

vasya · 21.10.2016 10:36:12

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. что вообще вы отите получить этим запросом?

x-miller-x · 21.10.2016 11:40:13

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

x-miller-x · 21.10.2016 12:00:22

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 записей единичек

vasya · 21.10.2016 12:22:30

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

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

vasya · 21.10.2016 14:49:23

x-miller-x написал:
4. Не совсем понял что надо делать

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

x-miller-x · 21.10.2016 14:58:09

Таблица user

Таблице notify_user

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

vasya · 21.10.2016 15:26:27

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

x-miller-x · 21.10.2016 16:22:16

о работает

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(*) сделать чтобы количество выводилось?

vasya · 21.10.2016 19:03:41

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

x-miller-x · 22.10.2016 07:44:27

вот результат выборки

а когда делаю select count(*)

а надо, чтобы выводил 5, то есть сумму

vasya · 22.10.2016 13:34:46

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;

Так?

x-miller-x · 23.10.2016 19:28:27

ага спасибо)

x-miller-x · 24.11.2016 15:06:41

 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)

vasya · 24.11.2016 15:12:25

http://sqlinfo.ru/forum/viewtopic.php?pid=44029#p44029

x-miller-x · 24.11.2016 16:19:07

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

x-miller-x · 24.11.2016 16:30:24

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

t1.tag_id NOT

IN (

SELECT id

FROM tag_stop

)

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

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

vasya · 24.11.2016 16:53:02

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

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

x-miller-x · 24.11.2016 17:06:59

vasya написал:
как минимум нужен индекс на tag_id

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

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

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

vasya · 24.11.2016 17:25:03

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

x-miller-x · 24.11.2016 17:51:47

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

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

#1 20.10.2016 17:02:00

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

#2 20.10.2016 17:10:25

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

#3 21.10.2016 07:53:41

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

#4 21.10.2016 10:36:12

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

#5 21.10.2016 11:40:13

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

#6 21.10.2016 12:00:22

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

#7 21.10.2016 12:22:30

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

#8 21.10.2016 14:49:23

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

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

#9 21.10.2016 14:58:09

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

#10 21.10.2016 15:26:27

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

#11 21.10.2016 16:22:16

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

#12 21.10.2016 19:03:41

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

#13 22.10.2016 07:44:27

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

#14 22.10.2016 13:34:46

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

#15 23.10.2016 19:28:27

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

#16 24.11.2016 15:06:41

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

#17 24.11.2016 15:12:25

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

#18 24.11.2016 16:19:07

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

#19 24.11.2016 16:30:24

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

#20 24.11.2016 16:53:02

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

#21 24.11.2016 17:06:59

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

vasya написал:

#22 24.11.2016 17:25:03

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

#23 24.11.2016 17:51:47

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

Board footer