LINUX.ORG.RU
Форум — Development  

Долгий запрос из вложенной таблицы

 


2

2

Добрый день! Не получается оптимизировать запрос MYSQL. Хотелось бы не использовать промежуточный приемник (PHP array).

Суть задачи: Выбрать сообщения из Table_1 для которых нет больше топиков на форуме (Table_2).

Table_1 - messages
---------------------
 id|topic_id|message|
---------------------
 1 | 80777  | mes1  |
---------------------
 2 | 80777  | mes2  |
---------------------
 3 | 80779  | mes3  |
---------------------

Table_2 - topics
--------------------------
   id  | posted | author |
--------------------------
 80776 | data   | name 1 |
--------------------------
 80777 | data   | name 2 |
--------------------------
 80778 | data   | name 3 |
--------------------------

mysql> SELECT topic_id, message FROM Table_1 WHERE topic_id NOT IN (SELECT id FROM Table_2);
Empty set (5.88 sec) !!!

По отдельности левая и правая часть запроса отрабатываются очень быстро. Предпринимал GROUP BY topic_id для Table_1с целью ускорения, но проблемы не решило кардинально.



Последнее исправление: morkovkin (всего исправлений: 1)
← Haskell для JVM: eta vs frege
программаторы →
Ответ на: комментарий от goingUp 
mysql>  explain SELECT topic_id, message FROM Table_1 WHERE topic_id NOT IN (SELECT id FROM Table_2);
+----+-------------+----------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table                | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+----------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | PRIMARY     | Table_1              | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 12055 |   100.00 | Using where |
|  2 | SUBQUERY    | Table_2              | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   497 |   100.00 | NULL        |
+----+-------------+----------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)
morkovkin
() автор топика
Ответ на: комментарий от goingUp 
+-------------------+-----------------------------------------------
| Table             | Create Table                                  
+-------------------+----------------------------------------------
| Table_1 | CREATE TABLE `Table_1` (
  `id` int(10) unsigned NOT NULL,
  `idparent` int(10) unsigned NOT NULL DEFAULT '0',
  `topic_id` int(10) unsigned NOT NULL,
  `author` int(10) unsigned NOT NULL,
  `message` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------------------+----------------------------------------------
1 row in set (0.00 sec)

+----------------------+-------------------------------------------
| Table                | Create Table                              
+----------------------+-------------------------------------------
| Table_2 | CREATE TABLE `Table_2` (
  `id` int(10) unsigned NOT NULL,
  `idcat` smallint(5) unsigned NOT NULL,
  `author` int(10) unsigned NOT NULL,
  `visib` tinyint(1) unsigned NOT NULL,
  `views` int(10) unsigned NOT NULL,
  `rating` int(11) NOT NULL,
  `posted` int(10) unsigned NOT NULL,
  `url` varchar(64) NOT NULL,
  PRIMARY KEY (`url`),
  UNIQUE KEY `posted` (`posted`),
  UNIQUE KEY `title` (`title`),
  UNIQUE KEY `url` (`url`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+----------------------+--------------------------------------------
1 row in set (0.00 sec)
morkovkin
() автор топика
Ответ на: комментарий от goingUp 
mysql>  show warnings;
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `user3k`.`Table_1`.`topic_id` AS `topic_id`,`user3k`.`Table_1`.`message` AS `message` from `user3k`.`Table_1` where (not(<in_optimizer>(`user3k`.`Table_1`.`topic_id`,`user3k`.`Table_1`.`topic_id` in ( <materialize> (/* select#2 */ select `user3k`.`Table_2`.`id` from `user3k`.`Table_2` where 1 ), <primary_index_lookup>(`user3k`.`Table_1`.`topic_id` in <temporary table> on <auto_key> where ((`user3k`.`Table_1`.`topic_id` = `materialized-subquery`.`id`))))))) |
+-------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
morkovkin
() автор топика
Ответ на: комментарий от morkovkin

Они не используются потому что их нету Ж8-() Добавь индекс по topic_id в первой таблице и по id во второй. Ну и почитай зачем нужны индексы.

UNIQUE KEY `title` (`title`),

автор от нас что-то скрывает)

goingUp ★★★★★
()
Ответ на: комментарий от goingUp

Спасибо!

Не скрываю что-то секретной, просто «грузить» не хочу и время отнимать лишней информацией. Еще раз спасибо за подсказки.

morkovkin
() автор топика
Ответ на: комментарий от goingUp 
explain SELECT topic_id, message FROM Table_1 WHERE topic_id NOT IN (SELECT id FROM Table_2);
+----+--------------------+----------------------+----------------+---------------+------+---------+------+-------+-------------+
| id | select_type        | table                | type           | possible_keys | key  | key_len | ref  | rows  | Extra       |
+----+--------------------+----------------------+----------------+---------------+------+---------+------+-------+-------------+
|  1 | PRIMARY            | Table_1              | ALL            | NULL          | NULL | NULL    | NULL | 12072 | Using where |
|  2 | DEPENDENT SUBQUERY | Table_2              | index_subquery | id            | id   | 4       | func |     1 | Using index |
+----+--------------------+----------------------+----------------+---------------+------+---------+------+-------+-------------+
2 rows in set (0.02 sec)
morkovkin
() автор топика
Ответ на: комментарий от morkovkin

20ms/~12k+500 rows

Всё равно время большое, подозреваю, что in (select ...) так и не оптимизировали и он так и дрочит индекс таблицы в подзапросе каждую запись. Попробуй переделать на left join.

crutch_master ★★★★★
()
Последнее исправление: crutch_master (всего исправлений: 3)
Ответ на: комментарий от crutch_master

Сначала был обратный эффект от использования LEFT JOIN. Время запроса выросло в 10 раз.

mysql> SELECT SQL_NO_CACHE topic_id, message FROM Table_1 WHERE topic_id NOT IN (SELECT id FROM Table_2);
3 rows in set (0.02 sec)

mysql> SELECT SQL_NO_CACHE topic_id, message FROM Table_1 left join Table_2 on topic_id = id where id is null
3 rows in set (0.21 sec)

Затем время сравнялось. Оставил как есть, мне кажется ничего не улучшилось.

morkovkin
() автор топика
Ответ на: комментарий от morkovkin

Затем время сравнялось.

Значит SQL_NO_CACHE как-то не так работает, да и вообще всё странно. 210мс это очень много для такой маленькой таблички. Возможно это всё таки кешированно, можно попробовать переподключиться к базе.

Оставил как есть, мне кажется ничего не улучшилось.

Да, на таких объёмах никакой разницы не будет, даже если она и есть.

crutch_master ★★★★★
() 

SELECT topic_id, message FROM Table_1 as t1
inner join (

select id from Table_1
minus
select id from Table_2

) as t2 on t1.id = t2.id

попробуй

bvn13 ★★★★★
()
Ответ на: комментарий от crutch_master

он так и дрочит индекс таблицы в подзапросе каждую запись

Так и есть суда по explain. Тип поменялся на DEPENDENT SUBQUERY и все такое. А есть что-то получше? В мане написано, что оно заменяет eq_ref join, ну если бы ОП сделал уникальный индекс по id во второй таблице (кстати это ОП еще один хинт для ускорения запроса) https://dev.mysql.com/doc/refman/8.0/en/explain-output.html#jointype_index_su...

goingUp ★★★★★
()
Последнее исправление: goingUp (всего исправлений: 1)
Ответ на: комментарий от looper

Время в 2 раза больше

SELECT topic_id, message FROM Table_1 WHERE NOT EXISTS (SELECT id FROM Table_2 WHERE Table_2.id = Table_1.topic_id);

3 rows in set (0.04 sec)
morkovkin
() автор топика
Ответ на: комментарий от goingUp

Так и есть суда по explain. Тип поменялся на DEPENDENT SUBQUERY и все такое. А есть что-то получше?

Left join, но он совсем выдаёт какую-то дичь.

crutch_master ★★★★★
()
Ответ на: комментарий от crutch_master

он совсем выдаёт какую-то дичь.

Ну то первый раз, может он другой индекс с диска подгружал. Потом же время стало такое же, так что по сути без разницы, что логично.

goingUp ★★★★★
()
Ответ на: комментарий от goingUp

Потом же время стало такое же

Запрос мог в кэш уйти, поэтому быстро стало.

crutch_master ★★★★★
()
Вы не можете добавлять комментарии в эту тему. Тема перемещена в архив.
← Haskell для JVM: eta vs frege
Development
программаторы →