MySQL optymalizacja zapytania


(system) #1

Pytanie dotyczy następującego przykładu:

show tables like "test_%";

drop table if exists test_task;

create table test_task ( id int auto_increment primary key, Code tinytext );

drop table if exists test_receiver;

create table test_receiver ( taskid int, receiverid int, primary key (taskid,receiverid) );

drop table if exists test_supplier;

create table test_supplier ( taskid int, supplierid int, primary key (taskid,supplierid) );

insert test_task (code) values ('Aaa'),('Bbb'),('Ccc'),('Ddd'),('Eee'),('Fff'),('Ggg'),('Hhh'),('Iii'),('Jjj'),('Kkk'),('Lll'),('Mmm'),('Nnn'),('Ooo'),('Ppp'),('Qqq'),('Rrr'),('Sss'),('Ttt'),('Uuu'),('Vvv'),('Www'),('Xxx'),('Yyy'),('Zzz');

insert test_receiver (taskid,receiverid) values (13,1),(13,2),(13,3),(13,5),(13,7),(13,11),(8,2),(8,4),(8,6);

insert test_supplier (taskid,supplierid) values (13,17),(13,19),(13,23),(12,24),(12,25),(12,26);

select 

  T.id Id,

  T.Code Kod,

  count(S.supplierid) IloscDost,

  count(R.receiverid) IloscOdb

from

  test_task T

left join

  test_supplier S on S.taskid=T.id

left join

  test_receiver R on R.taskid=T.id

group by

  T.Id

having

  IloscDost>0 or IloscOdb>0

;

select 

  T.id Id,

  T.Code Kod,

  count(distinct S.taskid,S.supplierid) IloscDost,

  count(distinct R.taskid,R.receiverid) IloscOdb

from

  test_task T

left join

  test_supplier S on S.taskid=T.id

left join

  test_receiver R on R.taskid=T.id

group by

  T.Id

having

  IloscDost>0 or IloscOdb>0

;

Pierwsze zapytanie ma dwie wady, po pierwsze zwraca niepoprawne odpowiedzi kiedy (IloscDost>1 oraz IloscOdb>1) po drugie działa na pewno baaaardzo długo ponieważ jeżeli IloscDost=1000 oraz IloscOdb=1000 to już będzie przeanalizowano milion rekordów !

Drugie zapytanie działa poprawnie, pytanie czy została druga wada - długie działanie ?

I następne pytanie, czy da się to zrobić bez left join?


(Airborn) #2

hmm, dlaczego milion?

natomiast co do optymalizacji, having jest często wolniejszy niż where


(system) #3

Ponieważ zapytanie:

select 

  T.id Id,

  T.Code Kod,

  S.supplierid,

  R.receiverid

from

  test_task T

left join

  test_supplier S on S.taskid=T.id

left join

  test_receiver R on R.taskid=T.id

where

  T.Id=13

;

Da wynik:

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

| Id | Kod | supplierid | receiverid |

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

| 13 | Mmm | 17 | 1 |

| 13 | Mmm | 17 | 2 |

| 13 | Mmm | 17 | 3 |

| 13 | Mmm | 17 | 5 |

| 13 | Mmm | 17 | 7 |

| 13 | Mmm | 17 | 11 |

| 13 | Mmm | 19 | 1 |

| 13 | Mmm | 19 | 2 |

| 13 | Mmm | 19 | 3 |

| 13 | Mmm | 19 | 5 |

| 13 | Mmm | 19 | 7 |

| 13 | Mmm | 19 | 11 |

| 13 | Mmm | 23 | 1 |

| 13 | Mmm | 23 | 2 |

| 13 | Mmm | 23 | 3 |

| 13 | Mmm | 23 | 5 |

| 13 | Mmm | 23 | 7 |

| 13 | Mmm | 23 | 11 |

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

18 rekordów (3*6) przy 3 dostawcach i 6 odbiorcach, więc przy 1000 dostawcach i 1000 odbiorcach to zapytanie zwróci 1000*1000=milion rekordów to akurat powoduje powolność tego zapytania.

Generalnie masz racje, ale tu akurat nie da się zastosować where:

select 

  T.id Id,

  T.Code Kod,

  count(distinct S.taskid,S.supplierid) IloscDost,

  count(distinct R.taskid,R.receiverid) IloscOdb

from

  test_task T

left join

  test_supplier S on S.taskid=T.id

left join

  test_receiver R on R.taskid=T.id

where

  IloscDost>0 or IloscOdb>0

group by

  T.Id

;

ERROR 1054: Unknown column 'IloscDost' in 'where clause'

No i oczywiste jest że to nie having jest winowajca :smiley: Znalazłem pewną alternatywę:

select 

  T.id Id,

  T.Code Kod,

  (select count(supplierid) from test_supplier where taskid=T.Id) IloscDost,

  (select count(receiverid) from test_receiver where taskid=T.Id) IloscOdb

from

  test_task T

having

  IloscDost>0 or IloscOdb>0

;

Niestety działa dopiero od MySQL 5.0 no i nie wiem czy to nie jest gorsze rozwiązanie, ponieważ odpala 2*N+1 zapytań gdzie N - ilość rekordów w tablice test_task.