|
PHP PHP aplikacije, Smarty, PEAR |
|
Alati teme | Način prikaza |
|
17. 03. 2006. | #1 |
Predrag Supurović
Grand Master
Datum učlanjenja: 24.01.2006
Lokacija: Užice
Poruke: 791
Hvala: 3
200 "Hvala" u 12 poruka
|
PHP brzi od MySQL-a u izvlacenju lookup vrednosti?!?!?
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?
__________________
Peđina beležnica (blog) - www.uzice.net - wireless.uzice.net - www.vokabular.org - www.vodic.net - forum.uzice.net |
17. 03. 2006. | #2 | |
član
Na probnom radu
Datum učlanjenja: 22.11.2005
Poruke: 40
Hvala: 0
0 "Hvala" u 0 poruka
|
Citat:
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. |
|
17. 03. 2006. | #3 |
Boris
Grand Master
Datum učlanjenja: 01.12.2005
Lokacija: Novi Sad
Poruke: 775
Hvala: 5
156 "Hvala" u 2 poruka
|
Sve i da je za nijansu sporije, opet je znacajno elegantnije, posebno kada je u pitanju vise od jednog joina.
__________________
"It’s important to have goals when you pet. Otherwise you’re just rubbing another mammal for no reason." - Scott Adams |
17. 03. 2006. | #4 |
Super Moderator
Invented the damn thing
Datum učlanjenja: 06.06.2005
Poruke: 2.371
Hvala: 370
701 "Hvala" u 194 poruka
|
Pa, kvaka je u tome da treba kreirati index za polje category_id u tabeli master. Nesto tipa:
Kôd:
alter table master add index category_id(category_id) |
18. 03. 2006. | #5 |
Ivan Dilber
Sir Write-a-Lot
|
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...
__________________
Leadership is the art of getting people to want to do what you know must be done. Poslednja izmena od ivanhoe : 18. 03. 2006. u 00:46. |
18. 03. 2006. | #6 |
Boris
Grand Master
Datum učlanjenja: 01.12.2005
Lokacija: Novi Sad
Poruke: 775
Hvala: 5
156 "Hvala" u 2 poruka
|
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...
Kôd:
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) Mrzelo me da kreiram jos koju tabelu i da vidim sta se onda desava...
__________________
"It’s important to have goals when you pet. Otherwise you’re just rubbing another mammal for no reason." - Scott Adams Poslednja izmena od zextra : 18. 03. 2006. u 01:44. |
18. 03. 2006. | #7 |
Super Moderator
Invented the damn thing
Datum učlanjenja: 06.06.2005
Poruke: 2.371
Hvala: 370
701 "Hvala" u 194 poruka
|
nisi u pravu jer:
Kôd:
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) |
18. 03. 2006. | #8 |
Ivan Dilber
Sir Write-a-Lot
|
moj mysql (4.1.9-max na windowsima) prikaze drugacije:
Kôd:
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)
__________________
Leadership is the art of getting people to want to do what you know must be done. Poslednja izmena od ivanhoe : 18. 03. 2006. u 04:09. |
|
|
Slične teme | ||||
Tema | Početna poruka teme | Forum | Odgovori | Poslednja poruka |
Upis brojcanih vrednosti sa zarezom i tackom u MySQL | martinluter | SQL baze podataka - Sponzor: Baze-Podataka.net | 3 | 19. 05. 2009. 22:21 |
Brzi prsti | crews_adder | Opušteno | 3 | 24. 02. 2006. 17:33 |
quick lookup | ivanhoe | Web site, dizajn i multimedia | 3 | 16. 01. 2006. 22:55 |