VPF::Select * FROM t1, t2 ... или LEFT JOIN ? Что быстр

gcc

Дата 19.8.2009, 07:46 (ссылка)

(нет голосов)

Загрузка ...

Агент алкомафии

Профиль
Группа: Участник
Сообщений: 2691
Регистрация: 25.4.2008
Где: %&й

Репутация: нет
Всего: 17

была ткая тема: http://forum.vingrad.ru/forum/topic-52054/unread-1.html

Что быстрее?

1 cлучай:

Код


SELECT * FROM t1, t2 WHERE t1.id=t2.id

2 cлучай:

Код


SELECT * FROM t1 LEFT JOIN t2 ON t1.id=t2.id WHERE t2.id IS NOT NULL

а как это в PgSQl

есть база 10гиг DBmail VDSManager

t1

Код


email_templates_pkey    CREATE UNIQUE INDEX email_templates_pkey ON email_templates USING btree (id)    
Primary key
    
No
    Cluster    Reindex    
template_id_index    CREATE UNIQUE INDEX template_id_index ON email_templates USING btree (id)    
Unique key
    
No
    Cluster    Reindex

t2
ничего нут в индексах

--------------------

Perl FAQ
freelance
perl, jquery, js, php
perl, jquery

Shaggie

Дата 19.8.2009, 08:09 (ссылка)

(нет голосов)

Загрузка ...

Опытный

Профиль
Группа: Завсегдатай
Сообщений: 570
Регистрация: 21.12.2006
Где: outer space

Репутация: 2
Всего: 72

Во-первых, должны быть равнозначны. Проверь генерируемые запросы через EXPLAIN <текст_запроса>.

Во-вторых, почему бы не заменить внешний джойн на внутренний, это проще и понятнее для чтения:

Код


SELECT * FROM t1 INNER JOIN t2 ON t1.id=t2.id

Слово INNER можно опустить, тип джойна внутренний по умолчанию, если не указано иначе.

--------------------

Цитата(alina3000 @ 6.3.2014, 10:47

)

Сорри что не по теме

Akina

Дата 19.8.2009, 08:09 (ссылка)

(нет голосов)

Загрузка ...

Советчик

Профиль
Группа: Модератор
Сообщений: 20581
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 3
Всего: 454

Цитата(gcc @ 19.8.2009, 08:46

)

2 cлучай

а почему не inner join? smile

--------------------

О(б)суждение моих действий - в соответствующей теме, пожалуйста. Или в РМ. И высшая инстанция - Администрация форума.

gcc

Дата 19.8.2009, 08:39 (ссылка)

(нет голосов)

Загрузка ...

Агент алкомафии

Профиль
Группа: Участник
Сообщений: 2691
Регистрация: 25.4.2008
Где: %&й

Репутация: нет
Всего: 17

на всех программа которых я видел на php+MySQL везде стоит LEFT JOIN иногда WHERE, на Oracle INNER JOIN

INNER JOIN - это тоже самое что срванить и через WHERE, если я правильно понял

тогда переделаю на INNER JOIN

Это сообщение отредактировал(а) gcc - 19.8.2009, 08:39

--------------------

Perl FAQ
freelance
perl, jquery, js, php
perl, jquery

DimW

Дата 19.8.2009, 09:30 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1330
Регистрация: 24.2.2005
Где: Орёл

Репутация: 2
Всего: 44

удалил.

Это сообщение отредактировал(а) DimW - 19.8.2009, 09:31

Akina

Дата 19.8.2009, 10:18 (ссылка)

(нет голосов)

Загрузка ...

Советчик

Профиль
Группа: Модератор
Сообщений: 20581
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 3
Всего: 454

Цитата(gcc @ 19.8.2009, 09:39

)

INNER JOIN - это тоже самое что срванить и через WHERE, если я правильно понял

Совсем не то же самое... вот, например, выдержка из справки по MySQL

Цитата

5.2.6. Как MySQL оптимизирует LEFT JOIN и RIGHT JOIN

Выражение "A LEFT JOIN B" в MySQL реализовано следующим образом:

Таблица B устанавливается как зависимая от таблицы A и от всех таблиц, от которых зависит A.

Таблица A устанавливается как зависимая ото всех таблиц (кроме B), которые используются в условии LEFT JOIN.

Все условия LEFT JOIN перемещаются в предложение WHERE.

Выполняются все стандартные способы оптимизации соединения, за исключением того, что таблица всегда читается после всех таблиц, от которых она зависит. Если имеется циклическая зависимость, MySQL выдаст ошибку.

Выполняются все стандартные способы оптимизации WHERE.

Если в таблице A имеется строка, соответствующая выражению WHERE, но в таблице B ни одна строка не удовлетворяет условию LEFT JOIN, генерируется дополнительная строка B, в которой все значения столбцов устанавливаются в NULL.

Если LEFT JOIN используется для поиска тех строк, которые отсутствуют в некоторой таблице, и в предложении WHERE выполняется следующая проверка: column_name IS NULL, где column_name - столбец, который объявлен как NOT NULL, MySQL пререстанет искать строки (для отдельной комбинации ключа) после того, как найдет строку, соответствующую условию LEFT JOIN.

Если почитать ещё и посравнивать - выяснится, что LEFT/RIGHT JOIN выполняется как INNER JOIN плюс дополнительные прибабахи. Которые процесс, ясен пень, не ускоряют.

--------------------

DimW

Дата 19.8.2009, 12:16 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1330
Регистрация: 24.2.2005
Где: Орёл

Репутация: 2
Всего: 44

Цитата(Akina @ 19.8.2009, 10:18

)

выдержка из справки

Akina, в выдержке нет ни слова про inner join, а втор спрашивал именно про него:

Цитата(gcc @ 19.8.2009, 08:39

)

INNER JOIN - это тоже самое что

Akina

Дата 19.8.2009, 12:23 (ссылка)

(нет голосов)

Загрузка ...

Советчик

Профиль
Группа: Модератор
Сообщений: 20581
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 3
Всего: 454

Цитата(DimW @ 19.8.2009, 13:16

)

в выдержке нет ни слова про inner join, автор спрашивал именно про него

Вот пусть и работает. Если автору это действительно нужно, он пойдёт на сайт мускула и сам почитает. Или найдёт аналогичный раздел в документации на свою СУБД. Мне оно сто лет не надо...
А если ему влом - может поверить на слово. Или не поверить.

--------------------

DimW

Дата 19.8.2009, 13:05 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1330
Регистрация: 24.2.2005
Где: Орёл

Репутация: 2
Всего: 44

речь не о том кому влом, а кому нет.
речь о том что ваш ответ:

Цитата(Akina @ 19.8.2009, 10:18

)

Совсем не то же самое

на вопрос:

Цитата(gcc @ 19.8.2009, 08:39

)

INNER JOIN - это тоже самое что срванить и через WHERE, если я правильно понял

не верен, так как обрабатываться оптимизатором должен одинаково, исключением может быть только глюкавость самого оптимизатора.

Akina

Дата 19.8.2009, 13:26 (ссылка)

(нет голосов)

Загрузка ...

Советчик

Профиль
Группа: Модератор
Сообщений: 20581
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 3
Всего: 454

Цитата(DimW @ 19.8.2009, 14:05

)

не верен, так как обрабатываться оптимизатором должен одинаково

Прочитайте ещё раз.

Код


select t1.*, t2.* 
from t1
inner join t2
on t1.id=t2.id

абсолютно то же (план исполнения должен быть идентичен), что и

Код


select t1.*, t2.* 
from t1, t2
where t1.id=t2.id

но вовсе не то же самое, что

Код


select t1.*, t2.* 
from t1
left join t2
on t1.id=t2.id
where t2.id is not null

В третьем варианте работы серверу будет больше - не думаю, что оптимизатор сможет правильно привести этот запрос к одному из первых двух.

Так вот именно эту неэквивалентность я и показываю.

--------------------

DimW

Дата 19.8.2009, 15:09 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1330
Регистрация: 24.2.2005
Где: Орёл

Репутация: 2
Всего: 44

Цитата(Akina @ 19.8.2009, 13:26

)

не думаю, что оптимизатор сможет правильно привести этот запрос к одному из первых двух

создаем таблицы и заполняем их данными(СУБД оракл):

Код


create table table1
  (id number not null)
/
alter table table1
  add constraint table1_pk primary key (id)
/
-- наполняю данными
begin
  for i in 1 .. 100000
  loop
    insert into table1
      values (i);
  end loop;
end;
/ 
commit
/
select count(*) from table1
/

create table table2
(table1_id number)
/
alter table table2
  add constraint table2_fk foreign key (table1_id)
  references table1 (id)
/
-- наполняю данными
begin
  for i in 1 .. 5
  loop
    for i in 1 .. 50000
    loop
      insert into table2
        values (i);
    end loop;    
  end loop;
end;
/
commit
/
select count(*) from table2
/

выполняем первый запрос:

Код


select count(*)
  from table1 t1
      ,table2 t2
 where t2.table1_id = t1.id;

смотрим план запроса:

Код


SELECT STATEMENT, GOAL = RULE                    
 SORT AGGREGATE                    
  NESTED LOOPS                    
   TABLE ACCESS FULL    JDBF    TABLE2            
   INDEX UNIQUE SCAN    JDBF    TABLE1_PK

второй запрос:

Код


select count(*)
  from table1 t1
      inner join table2 t2 on t2.table1_id = t1.id

план:

Код


SELECT STATEMENT, GOAL = RULE                    
 SORT AGGREGATE                    
  NESTED LOOPS                    
   TABLE ACCESS FULL    JDBF    TABLE2            
   INDEX UNIQUE SCAN    JDBF    TABLE1_PK

третий запрос:

Код


select count(*)
  from table1 t1
      left join table2 t2 on t2.table1_id = t1.id 
 where t2.table1_id is not null

план:

Код


SELECT STATEMENT, GOAL = RULE                    
 SORT AGGREGATE                    
  NESTED LOOPS                    
   TABLE ACCESS FULL    JDBF    TABLE2            
   INDEX UNIQUE SCAN    JDBF    TABLE1_PK

из преведенных планов видно что они идентичны. статистику выкладывать не буду, поверьте мне на слово - она тоже совпадает.
не нужно данный пример расценивать как руководство к действию т.к. пример был приведен из конкретной СУБД конкретной версии!

Akina

Дата 19.8.2009, 15:24 (ссылка)

(нет голосов)

Загрузка ...

Советчик

Профиль
Группа: Модератор
Сообщений: 20581
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 3
Всего: 454

Цитата(DimW @ 19.8.2009, 16:09

)

из преведенных планов видно что они идентичны

Если не влом - план для последнего запроса, но без where, не затруднит?

--------------------

DimW

Дата 19.8.2009, 15:30 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1330
Регистрация: 24.2.2005
Где: Орёл

Репутация: 2
Всего: 44

Цитата(Akina @ 19.8.2009, 15:24

)

Если не влом - план для последнего запроса, но без where, не затруднит?

Код


select count(*)
  from table1 t1
      left join table2 t2 on t2.table1_id = t1.id

Код


SELECT STATEMENT, GOAL = RULE 
 SORT AGGREGATE      
  HASH JOIN OUTER    
   TABLE ACCESS FULL  JDBF  TABLE1 
   TABLE ACCESS FULL  JDBF  TABLE2

если что: Oracle9i Enterprise Edition Release 9.2.0.8.0

Akina

Дата 19.8.2009, 15:36 (ссылка)

(нет голосов)

Загрузка ...

Советчик

Профиль
Группа: Модератор
Сообщений: 20581
Регистрация: 8.4.2004
Где: Зеленоград

Репутация: 3
Всего: 454

Умный, собака smile

не все СУБД так умеют.

--------------------

DimW

Дата 19.8.2009, 15:44 (ссылка)

(нет голосов)

Загрузка ...

Эксперт

Профиль
Группа: Завсегдатай
Сообщений: 1330
Регистрация: 24.2.2005
Где: Орёл

Репутация: 2
Всего: 44

Цитата(Akina @ 19.8.2009, 15:36

)

Умный, собака

ага

Цитата(Akina @ 19.8.2009, 15:36

)

не все СУБД так умеют

я думаю что и на оракле не во всех случаях планы будут такими.
чесно говоря я и сам до последнего не верил что получится, но должен был проверить. smile

0 Пользователей читают эту тему (0 Гостей и 0 Скрытых Пользователей)
0 Пользователей:
« Предыдущая тема \| PostgreSQL \| Следующая тема »