PDA

Pogčedajte punu verziju : PHP brzi od MySQL-a u izvlacenju lookup vrednosti?!?!?


Pedja
17. 03. 2006., 21:21
Nesto diskutovah sa prijateljem i on mi na delu pokaza da je PHP brzi do MySQL-a u izvlacenju lookup vrednosti iz sifarnika.

Radi se o sledecem. Imamo dve tabele:

master (
master_id int(10) unsigned NOT NULL auto_increment,
category_id int(10) unsigned ,
PRIMARY KEY (master_id)

i

categories (
category_id int(10) unsigned NOT NULL DEFAULT '0' ,
description varchar(30) ,
PRIMARY KEY (category_id)


Tabela master ima veliki broj slogova, a potrebno je prikazati sve slogove tako da se umesto category_id prikazuje categories.description.

Ja sam zagovarao da to treba uraditi preko SQL JOIN a on da je bolje prvo pokupiti vrednosti iz categories u array, ond auraditi select iz master i priliom prikazivanaj svakog sloga, rucno iz array vaditi description i prikazivati ga.

Covek je napravio test i pokazao da stvarno PHP to radi znacajno brze nego SQL JOIN.

U prilogu je arhiva sa testom, tako da mozete i sami da probate.

Ja sve mislim da tu ima neka kvaka, mada mi nije jasno koja. Obratio sam paznju, i cak je zauzece memorije otprilike podjednako kada se radi na oba nacina, samo sto PHP to uradi brze.

U testu imate skript koji generise testne podatke, u jednom prolazu puni bazu sa 40000 slogova. Ako zelite vise slogova samo vise puta pokrenite taj skript.

Na vecem broju slogova PHP ispadne i do 25% brzi.

Moze li neko ovo da objasni?

Srpko
17. 03. 2006., 22:11
i cak je zauzece memorije otprilike podjednako kada se radi na oba nacina, samo sto PHP to uradi brze.


description u tabeli categories je varchar i otprilike zauzima 6 bajta po rekordu u ovom slucaju u totalu 6 kb, kada uradis join onda dobijas 40000 * 6 sto je 240 kb
Mislim da se ovde stvara razlika u brzini.
U ovom slucaju bi svakako izabrao php resenje ali mnogim drugim slucajevima left join je ipak elegantnije i brze resenje.

zextra
17. 03. 2006., 22:19
Sve i da je za nijansu sporije, opet je znacajno elegantnije, posebno kada je u pitanju vise od jednog joina.

dinke
17. 03. 2006., 22:23
Pa, kvaka je u tome da treba kreirati index za polje category_id u tabeli master. Nesto tipa:

alter table master add index category_id(category_id)

U protivnom, prilikom svakog spajanja mysql mora da prodje kroz celu tabelu sto je visestruko puta sporiji proces.

ivanhoe
18. 03. 2006., 01:43
mozda bi bilo dovoljno promeniti redosled tabela u joinu, jer ako se na svaki master dodaje description, onda nije potreban dodatni index, valjda ? Ako radi suprotno, onda je problem, mora ovo sto Dinke rece.. U svakom slucaju treba videti sa Explain zasto se to desava..

Problem sa tehnikom gde se to radi sa php-om je (pored ruznoce) ako imas puno rekorda, sto ti svi stoje u memoriji servera u asoc. nizu koji zauzima mnogo vise memorije nego sto ima elemenata (jer je to u stvari hash tabela). Plus ako se nesto kopira i slicno, pa zavrsis sa 3-4 verizje istih 400000+ podataka, mozes lako da lupis u memory limit... plus ako imas vise pristupa istovremeno toj skripti onda server gubi dragocenu memoriju...

zextra
18. 03. 2006., 02:41
Dodatni indeks na tabeli master nije potreban jer mysql automatski pravi relaciju one-to-many izmedju tabela categories i master, jer je polje category_id u tabeli categories primarni kljuc. Dakle problem ne lezi u tome...


mysql> explain select * from categories,master where categories.category_id=master.category_id;
+----+-------------+------------+--------+---------------+---------+---------+-------------------------+-------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------------------------+-------+-------+
| 1 | SIMPLE | master | ALL | NULL | NULL | NULL | NULL | 40000 | |
| 1 | SIMPLE | categories | eq_ref | PRIMARY | PRIMARY | 4 | test.master.category_id | 1 | |
+----+-------------+------------+--------+---------------+---------+---------+-------------------------+-------+-------+
2 rows in set (0.00 sec)


Dodatni indeksi ne mogu da poprave performanse upita, jer se i tako i tako citaju svi zapisi iz tabele master.

Mrzelo me da kreiram jos koju tabelu i da vidim sta se onda desava... :)

dinke
18. 03. 2006., 03:56
nisi u pravu jer:

mysql> explain SELECT m.*, c.description FROM master m
-> LEFT JOIN categories c ON m.category_id = c.category_id;
+----+-------------+-------+--------+---------------+---------+---------+-----------------------+-------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-----------------------+-------+-------+
| 1 | SIMPLE | m | ALL | NULL | NULL | NULL | NULL | 40427 | |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | measure.m.category_id | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+-----------------------+-------+-------+
2 rows in set (0.00 sec)

mysql> alter table master add index category_id(category_id);
Query OK, 40000 rows affected (1.06 sec)
Records: 40000 Duplicates: 0 Warnings: 0

mysql> explain SELECT m.*, c.description FROM master m
-> LEFT JOIN categories c ON m.category_id = c.category_id;
+----+-------------+-------+--------+---------------+-------------+---------+-----------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+-------------+---------+-----------------------+-------+-------------+
| 1 | SIMPLE | m | index | NULL | category_id | 5 | NULL | 40715 | Using index |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | measure.m.category_id | 1 | |
+----+-------------+-------+--------+---------------+-------------+---------+-----------------------+-------+-------------+
2 rows in set (0.00 sec)

Dakle, nakon dodavanja index on se koristi (type kolona u explain outputu).

ivanhoe
18. 03. 2006., 05:03
moj mysql (4.1.9-max na windowsima) prikaze drugacije:

mysql> alter table master add index category_id(category_id);
Query OK, 177693 rows affected (1.80 sec)
Records: 177693 Duplicates: 0 Warnings: 0

mysql> explain SELECT m.*, c.description FROM master m, categories c WHERE m.category_id = c.category_id;
+----+-------------+-------+--------+---------------+---------+---------+-----------------------+--------+----
---+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Ext
ra |
+----+-------------+-------+--------+---------------+---------+---------+-----------------------+--------+----
---+
| 1 | SIMPLE | m | ALL | category_id | NULL | NULL | NULL | 177693 |
|
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | measure.m.category_id | 1 |
|
+----+-------------+-------+--------+---------------+---------+---------+-----------------------+--------+----
---+
2 rows in set (0.00 sec)

mysql> explain SELECT m.*, c.description FROM master m LEFT JOIN categories c ON m.category_id = c.category_id
;
+----+-------------+-------+--------+---------------+---------+---------+-----------------------+--------+----
---+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Ext
ra |
+----+-------------+-------+--------+---------------+---------+---------+-----------------------+--------+----
---+
| 1 | SIMPLE | m | ALL | NULL | NULL | NULL | NULL | 177693 |
|
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | measure.m.category_id | 1 |
|
+----+-------------+-------+--------+---------------+---------+---------+-----------------------+--------+----
---+
2 rows in set (0.00 sec)


Odnosno gledace sva polja tabele master, i za inner i za left join, bas kao i kad nema indexa.. sto se pokaze i kad se pusti ovaj Pedjin test, jer se dobije skoro isti rezultati...bar sa myisam tabelama, kasno je pa me mrzi da probam innoDB kako se ponasa...

degojs
18. 03. 2006., 07:18
I kod mene sa ili bez indexa daje rezultate kao kod ivanhoe sto i jeste za ocekivati jer kako zextra rece - ionako se gledaju svi zapisi iz master tabele.

Inace samo dodavanje indexa kako dinke rece kod mene nista nije ubrzalo upit.

Ali, ako se stavi index pa zatim uradi INNER JOIN, rezultati su bolji za oko 30%.

Interesantno, zar ne :)

Pedja
18. 03. 2006., 08:41
Koliko znam, samim tim sto postavis primarni kljuc tabele on predstavlja i indeks, tako da nema potrebe da se postavlja poseban index po istom polju.

No probao sam da stavim dodatne indekse i description polje sam promenio u CHAR umesto vARCHAR. Nema razlike. I dalje je PHP brzi.

Stoji da je SQL JOIN elegantniji samo me svrbi to sto je sporije, cisto iz principijelnih razloga. Do juce sam smeo da se zakunem da nista ne moze biti brze osim u specijalnim slucajevima.

Cini mi se da je ovde kvaka u asocijativnom nizu PHP-a posto to izgleda radi vraski brzo, bez obzira na poredjenje sa MySQL-om. Ne znam kako to radi interno, ali mi se nikako ne uklapa da je brze nego sto to radi MySQL.

Zanimljivo je da razlika u brzini opada kako raste broj slogova u master tabeli, odnosno ostaje priblizno ista u apsolutnoj vrednosti (kod mene je to oko jedne sekunde).

Bilo bi zanimljivo videti ovaj test pusten na nekom drugom serveru.

Inace, u realnnoj upotrebi ovakav upit je po pravilu ogranicen na izdvajanje svega desetak do pedeset slogova. Test vuce celu master tabelu zbog merenja. Tada razlika u iskoriscenju resursa postaje zanemarljiva ali je covek u stvari napravio odicno resenje, jer SQL upiti se generisu dinamicki i razdvajanjem lookup-a od osnovne tabele, covek je znatno pojednostavio stvar, resio se goleme bede oko uklapanja svih joinova, omogucio znatno vecu upotrebljivost lookup-a (post sada nije ogranicen JOIN-ovima moze da dozvoli da maksimalno parametrizuje upit koji daje lookup tabelu, ukljucujuci i mogucnost da se custom napise ceo SELECT), i jos, kao slag na tortu, dobio vecu brzinu.

ivanhoe
18. 03. 2006., 09:16
Cini mi se da je ovde kvaka u asocijativnom nizu PHP-a posto to izgleda radi vraski brzo, bez obzira na poredjenje sa MySQL-om. Ne znam kako to radi interno, ali mi se nikako ne uklapa da je brze nego sto to radi MySQL.


pa asocijativni niz je svakako brzi jer on:
1. ima uvek sve podatke u memoriji (indexi se keshiraju, ali su ipak na disku)
2. radi kao hash tabela, a mysql indexi su valjda B-stabla. Hash na osnovu kljuca racuna formulom direktno poziciju podatka i pristupa mu direktno(ili u par koraka kod kolizija), dok stablo zahteva spustanje po cvorovima...

medjutim priznajem da nisam imao pojma da je tolika razlika..

postoji jos jedan problem kod mysql join-ova, kad ima puno podataka u tabela nad kojima se radi join. Ako ih pustis na 2 tabele od kojih jedna ima npr. 20 miliona recorda mozes slobodno da zaboravis na server, mrtav je, sa ili bez indexa. Pravio sam neke spajdere koji skupljaju uzasno mnogo podataka, i morali smo da splitujemo tabele svakih milion, milion i po recorda, jer cela baza postane uzasno spora...

zextra
18. 03. 2006., 11:16
Apropo indeksa

Kod mene je isti slucaj kao i kod ivanhoe da se index na polju category_id ne koristi, ali obratite paznju sta se desava u sledecim situacijama.


mysql> create index test on master (category_id,master_id);
Query OK, 40000 rows affected (0.31 sec)
Records: 40000 Duplicates: 0 Warnings: 0

mysql> explain SELECT m.*, c.description FROM master m LEFT JOIN categories c ON m.category_id = c.category_id;
+----+-------------+-------+--------+---------------+---------+---------+--------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+--------------------+-------+-------------+
| 1 | SIMPLE | m | index | NULL | test | 9 | NULL | 40000 | Using index |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | news.m.category_id | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+--------------------+-------+-------------+
2 rows in set (0.01 sec)

mysql> SELECT m.*, c.description FROM master m LEFT JOIN categories c ON m.category_id = c.category_id;

................results bla bla.......
40000 rows in set (0.15 sec)

mysql> create index test on master (master_id,category_id);
Query OK, 40000 rows affected (0.29 sec)
Records: 40000 Duplicates: 0 Warnings: 0

mysql> explain SELECT m.*, c.description FROM master m LEFT JOIN categories c ON m.category_id = c.category_id;
+----+-------------+-------+--------+---------------+---------+---------+--------------------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+--------------------+-------+-------------+
| 1 | SIMPLE | m | index | NULL | test | 9 | NULL | 40000 | Using index |
| 1 | SIMPLE | c | eq_ref | PRIMARY | PRIMARY | 4 | news.m.category_id | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+--------------------+-------+-------------+
2 rows in set (0.00 sec)

mysql> SELECT m.*, c.description FROM master m LEFT JOIN categories c ON m.category_id = c.category_id;
................results bla bla.......
40000 rows in set (0.44 sec)

mysql> drop index test on master;
Query OK, 40000 rows affected (0.22 sec)
Records: 40000 Duplicates: 0 Warnings: 0

mysql> SELECT m.*, c.description FROM master m LEFT JOIN categories c ON m.category_id = c.category_id;
................results bla bla.......
40000 rows in set (0.37 sec)



Da napomenem da je situacija sa rezultatima identicna bilo da je u pitanju INNER ili LEFT join. Vreme izvrsavanja je bilo identicno i posle vise izvrsavanja istog select-a.

Jasno mi je da do poboljsanja u brzini moze doci zahvaljujuci indeksiranom citanju cele tabele master. Ocigledno je da redosled igra ulogu, ali zasto je zapravo select sporiji kada je master_id na prvom mestu u indeksu (tj kada je category_id na prvom mestu)?

Ono sto je jos zanimljivije - kako to da explain ne primeti anomaliju koja ocigledno dovodi do usporenja prilikom selecta?

dinke
18. 03. 2006., 12:46
moj mysql (4.1.9-max na windowsima) prikaze drugacije:
...

Odnosno gledace sva polja tabele master, i za inner i za left join, bas kao i kad nema indexa.. sto se pokaze i kad se pusti ovaj Pedjin test, jer se dobije skoro isti rezultati...bar sa myisam tabelama, kasno je pa me mrzi da probam innoDB kako se ponasa...
Kod mene MySQL 4.1.8-nt innodb tabele. Otud razlika u explain-u.

dee
02. 08. 2006., 17:40
MySQL 4.0.26.

na InnoDB tablicama, rezultati su podjednaki, a JOIN je u prosjeku nesto brzi. jedan od rezultata:
===============================

Summary: Join took 0.28575801849365 . Separate queries took 0.36347579956055


sa MyISAM tablicama, rezultati su podjednaki, iako su separate queries u prosjeku neznatno brzi. jedan od rezultata:
===============================

Summary: Join took 0.65324687957764 . Separate queries took 0.58501100540161



Nesto mi ne valja trenutno sa MySQL serverom pa ne mogu kreirati foreign keys na master tabeli. je li probao netko to i sa kakvim rezultatima?