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

kshvakov · 22.06.2011 00:13:59

Ситуация аналогична http://sqlinfo.ru/forum/viewtopic.php?id=3880
Вопрос: всетаки почему запросы вида

select t.* from test t join (select name from test group by name having count(name)>1) t1 on t.name=t1.name;

выполняются гораздо быстрее

select * from test where name in (select name from test group by name having count(name)>1);

Работа первого запроса понятна, но вот почему подзапросы в where ведут себя так, причем раз на раз не приходиться.
Не смог найти информации о том как работает MySql с подобными подзапросами и почему 2 раздельных запроса вида

select tid from t; select * from p where id in (tid);

будут работать в разы быстрее

 select * from p where id in (select tid from t);

paulus · 22.06.2011 16:37:50

Посмотрите на EXPLAIN обоих запросов. Скорее всего, второй обрабатывается как
зависимый подзапрос.

kshvakov · 22.06.2011 16:54:44

да, он действительно выполняется как зависимый, но, тем не менее, тот же EXPLAIN показывает одинаковое количество строк используемых в выборке, использование индекса, а Extra просто "using where". В тоже время в запросе с join тип derived, все остальное примерно идентично, + создается производная таблица по которой идет сортировка.

Вопрос почему , а главное как, в MySql работают те самые зависимые запросы, почему такая огромная разница с производными ?

evgeny · 22.06.2011 23:59:56

Вот profile двух запросов как ваши.

Это пример с JOIN

starting 0.000057
Opening tables 0.000109
System lock 0.000002
Table lock 0.000042
optimizing 0.000005
statistics 0.000009
preparing 0.000005
executing 0.000003
Sorting result 0.000002
Sending data 0.000495
init 0.000015
optimizing 0.000007
statistics 0.000070
preparing 0.000014
executing 0.000001
Sending data 0.003005
end 0.000001
query end 0.000001
freeing items 0.000111
removing tmp table 0.000008
closing tables 0.000003
logging slow query 0.000001
cleaning up 0.000003

Это с WHERE IN

starting 0.000117
Opening tables 0.000013
System lock 0.000002
Table lock 0.000006
init 0.000036
optimizing 0.000007
statistics 0.000057
preparing 0.000008
executing 0.000002
Sending data 0.000033
optimizing 0.000004
statistics 0.000006
preparing 0.000005
executing 0.000004
Sorting result 0.000002

----------------------------------------------
Sending data 0.000448
executing 0.000001 Повторяется около 2000 раз
Sorting result 0.000002
----------------------------------------------

Sending data 0.000470
end 0.000005
query end 0.000003
freeing items 0.000278
logging slow query 0.000001
cleaning up 0.000010

Интересно почему WHERE IN так коряво работает ? Что физически происходит ?

Отредактированно evgeny (23.06.2011 00:04:11)

paulus · 27.06.2011 14:52:01

Нужно уметь точно отличать случаи зависимых и независимых подзапросов.
К сожалению, оптимизация этого куска всё время откладывается. Вроде бы,
в 6.0 обещают какое-то улучшение кода в этом месте.

kshvakov · 28.06.2011 17:28:22

Видимо пока лучше забыть про вложенные запросы в MySql

simple · 28.06.2011 18:15:05

В конструкции In помоему индексы не должны работать, отсюда может и корявая работа.

kshvakov · 28.06.2011 18:19:12

В IN индексы работают, чем так IN не устроил )

mysql> explain select * from test where id in (54631, 2546, 587);

+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+

| id | select_type | table       | type  | possible_keys | key     | key_len | ref  | rows | Extra       |

+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+

|  1 | SIMPLE      | test | range | PRIMARY       | PRIMARY | 4       | NULL |    3 | Using where |

+----+-------------+-------------+-------+---------------+---------+---------+------+------+-------------+

1 row in set (0.00 sec)

LazY · 03.07.2011 05:29:03

Нужно уметь точно отличать случаи зависимых и независимых подзапросов.

А можно пару слов о том, как точно это делать?

evgeny · 03.07.2011 15:29:11

LazY написал:
Нужно уметь точно отличать случаи зависимых и независимых подзапросов.
А можно пару слов о том, как точно это делать?

Ну вот по профайлингу и видно когда зависимый а когда нет, смотрите мой пример выше.

Каждый подзапрос виден как
----
Sending data 0.000448
executing 0.000001
---

Если вы имеете виду как отличать по самому запросу, то тут наверно нужно просто помнить конкретные конструкции ...
Например:
SELECT customer_id,(SELECT first_name FROM users where user_id=customer_id) as customer_name FROM customers;
Так же примеры с тем же WHERE IN
Насколько я знаю таких примеров не много ... Если кто знает , привидите примеры, интересно посмотреть ...

Отредактированно evgeny (03.07.2011 15:49:39)

LazY · 03.07.2011 15:39:42

А, в смысле

Повторяется около 2000 раз

- это буквально, в листинге от профайлинга?

evgeny · 03.07.2011 15:50:41

LazY написал:
Нужно уметь точно отличать случаи зависимых и независимых подзапросов.
А можно пару слов о том, как точно это делать?

Ну вот по профайлингу и видно когда зависимый а когда нет, смотрите мой пример выше.

Каждый подзапрос виден как
----
Sending data 0.000448
executing 0.000001
---

Если вы имеете виду как отличать по самому запросу, то тут наверно нужно просто помнить конкретные конструкции ...
Например:
SELECT customer_id,(SELECT first_name FROM users where user_id=customer_id) as customer_name FROM customers;
Так же примеры с тем же WHERE IN
Насколько я знаю таких примеров не много ... Если кто знает , привидите примеры, интересно посмотреть ...

evgeny · 03.07.2011 15:53:17

LazY написал:
А, в смысле
Повторяется около 2000 раз
- это буквально, в листинге от профайлинга?

Нет это мой коммент :-)
В профалинге это часть повторялась 2000 раз

LazY · 04.07.2011 08:47:05

Ну да, я так и понял

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

#1 22.06.2011 00:13:59

подзапросы в where и join

#2 22.06.2011 16:37:50

Re: подзапросы в where и join

#3 22.06.2011 16:54:44

Re: подзапросы в where и join

#4 22.06.2011 23:59:56

Re: подзапросы в where и join

#5 27.06.2011 14:52:01

Re: подзапросы в where и join

#6 28.06.2011 17:28:22

Re: подзапросы в where и join

#7 28.06.2011 18:15:05

Re: подзапросы в where и join

#8 28.06.2011 18:19:12

Re: подзапросы в where и join

#9 03.07.2011 05:29:03

Re: подзапросы в where и join

#10 03.07.2011 15:29:11

Re: подзапросы в where и join

LazY написал:

#11 03.07.2011 15:39:42

Re: подзапросы в where и join

#12 03.07.2011 15:50:41

Re: подзапросы в where и join

LazY написал:

#13 03.07.2011 15:53:17

Re: подзапросы в where и join

LazY написал:

#14 04.07.2011 08:47:05

Re: подзапросы в where и join

Board footer