ovako, prvo sto terba da uradis da bi dobio na brzini je:
Code:
-- napravi administratori tabelu da bude fixed
alter table administratori modify korisnicko_ime char(40), modify ime_prezime char(40);
-- napravi kategorije tabelu da bude fixed
alter table kategorije modify naziv char(40);
-- ista prica sa tabelom vijesti
-- ovde sada imas dupli problem, ajde sto je TEXT za "uvod" cinio tabelu "dynamic"
-- (sto je za myisam dosta sporije od fixed) vec dodatno za svaki join sve kreirane
-- temp tabele ce biti na disku sto je znacajno sporije nego u ram-u (bez obzira koliko mu
-- das memorije za temp/heap tabele)
-- inace kada radis join UVEK se kreira temp tabela (bez obzira da li to explain prikaze ili ne)
--
-- ako ne mozes da smestis uvod u 255 karaktera, napravi zasebnu tabelu gde drzis uvod
-- pa ga povuci preko id-a kada budes morao ili ostavi TEXT ali onda konfigurisi server tako
-- da ti je tempdir na ram disku
alter table vijesti modify naslov char(100), modify mala_slika char(100), modify uvod char(255);
pre ove promene, sa ovom 20K slogova tabelom upit (iz originalnog posta) je trajao na mom desktopu izmedju 0.04 i 0.05 sekundi. posle ove promene (bez ikakve promene indexa / upita) upit traje 0.01 do 0.02sec
ako vratim uvod na TEXT brzina je 0.03 - 0.04
ako sada mysql namestim tako da u my.cnf dodam tmpdir=/dev/shm .. dakle kazem mu da koristi ram za mysql temp dir .. eh .. i nikakve promene ... 0.03 opet ...
jbg .. mnoooooooogo je brz .. sve to ispod 0.1sec je statisticka greska :) ... teba mi neka sporija masina za ovo testiranje :D
sve u svemu - osnovna sporost upita ti je u tome sto je v.uvod TEXT sto znaci da ce mysql da kreira temp tabelu na disku sto znaci da je upit sam po sebi ultra spor ... da bi to izbegao, ili izvbaci v.uvod iz upita ili ako moze da se uglavi u char(255) promeni ga u to, ili konfigurisi mysql da mu je tmpdir na ram disku kako bi zaobisao taj problem...
evo ga jedan "update vijesti set uvod=repeat(uvod, 10000);"
sada upiti traju malo duze :D
sa tmpdirom setovanim da pokazuje na /tmp (default) .. upit traje od 2.02 do 4.42sec zavisno od toga da li nesto drugo na masini trosi disk io... kada prebacim da je tmpdir na /dev/shm (ram disk) isti upit traje od 0.33 do 0.57sec
pritom, uzmi u obzir da masina "ne radi nista", dakle IO mi je free ... da imam konkurentne upite + neki apache koji drlja disk .. ovaj sa ram diskom bi ostao na 0.3-0.6sec dok bi onaj sto zavisi od io-a otisao na mnoooooooogo vise (pritom, ova masina ima 8G rama i ne radi nista trenutno dakle preko 4G rama je disk cache sto je dodatno pomoglo za onaj io bound query).
sad malo prebudzimo query ...
Code:
SELECT SQL_NO_CACHE
v.id, v.naslov, a.ime_prezime, v.mala_slika, v.uvod
FROM
vijesti v
JOIN kategorije k ON (k.kategorija_id = v.kategorija)
JOIN administratori a ON (v.autor = a.id)
WHERE
v.tip = 1
AND v.media = 0
AND v.pozicija
IN ('5', '6', '7', '8', '9', '1000')
AND k.parent_id IN ( 4, 5 )
AND v.id NOT IN ( 22083, 22075, 22077, 22070 )
AND v.datum_objave <= '2009-08-13 10:58:00'
ORDER BY v.pozicija, v.datum_objave DESC
LIMIT 5;
isto to samo malo drugacije .. optimizer "manje pogadja" .. dobili smo ovde u proseku 0.1s ali onda ..
Code:
SELECT SQL_NO_CACHE
v.id, v.naslov, a.ime_prezime, v.mala_slika, v.uvod
FROM
administratori a
JOIN vijesti v ON (v.autor = a.id)
JOIN kategorije k ON (k.kategorija_id = v.kategorija)
WHERE
v.tip = 1
AND v.media = 0
AND v.pozicija
IN ('5', '6', '7', '8', '9', '1000')
AND k.parent_id IN ( 4, 5 )
AND v.id NOT IN ( 22083, 22075, 22077, 22070 )
AND v.datum_objave <= '2009-08-13 10:58:00'
ORDER BY v.pozicija, v.datum_objave DESC
LIMIT 5;
i dobijemo ispod 2s
e sada "ovo nije normalno" :D ... dakle to je fora sto optimizer nekad ne odradi posao kako treba .... tako da realno to mozemo da zanemarimo :D ali je zgodno za spomenuti :D
ono sto je u celoj prici bitno je
Code:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: k
type: range
possible_keys: PRIMARY,parent_id
key: parent_id
key_len: 4
ref: NULL
rows: 10
Extra: Using where; Using temporary; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: v
type: ref
possible_keys: PRIMARY,datum_objave,kategorija
key: kategorija
key_len: 4
ref: es.k.kategorija_id
rows: 435
Extra: Using where
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: a
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: es.v.autor
rows: 1
Extra:
3 rows in set (0.00 sec)
ovo nije optimalno ... da pogledamo upit sam za sebe ..
Code:
sta: v.id, v.naslov, a.ime_prezime, v.mala_slika, v.uvod -- prvi problem, v.uvod je text znaci imamo temp tabele na disku
kako:
v.autor =
v.tip =
v.media =
v.pozicija IN
v.id NOT IN
v.datum_objave <=
v.kategorija =
k.parent_id IN
k.kategorija_id = v.kategorija
a.id =
sortiraj: v.pozicija, v.datum_objave
bez gledanja u tabele ... "ovde ima puno polja koja bi bilo zgodno da su indexirana" ... a mysql ne ume da koristi mnogo indexa u istom upitu ...
krenemo od pozadi ... administratori .. koristi se samo id .. on je primarni kljuc .. tabela ima tri ipo sloga .. dakle tu nista nema sta da se pipa ...
onda kategorije .. tu nam treba kompozitni kljuc, dakle
Code:
alter table kategorije modify kategorija_id int(5);
alter table kategorije drop key parent_id;
alter table kategorije drop key naziv;
alter table kategorije drop primary key;
alter table kategorije add primary key (kategorija_id, parent_id);
alter table katogorije modify kategorija_id int(5) auto_increment;
dakle, sada tabela treba da izgleda:
Code:
mysql> show create table kategorije\G
*************************** 1. row ***************************
Table: kategorije
Create Table: CREATE TABLE `kategorije` (
`kategorija_id` int(5) NOT NULL AUTO_INCREMENT,
`parent_id` int(5) NOT NULL DEFAULT '0',
`naziv` char(40) DEFAULT NULL,
PRIMARY KEY (`kategorija_id`,`parent_id`)
) ENGINE=MyISAM AUTO_INCREMENT=159 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
i sada upit traje 0.3sekunde ... dakle vise nego 10 puta krace ...
explain je blago drugaciji:
Code:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: v
type: range
possible_keys: PRIMARY,datum_objave,kategorija
key: datum_objave
key_len: 9
ref: NULL
rows: 14185
Extra: Using where; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: k
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: es.v.kategorija
rows: 1
Extra: Using where; Using index
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: a
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: es.v.autor
rows: 1
Extra:
3 rows in set (0.00 sec)
sad nam jos ostaju vesti ...
da probamo najtruluju foru:
Code:
alter table vijesti add key (id, autor, tip, media, pozicija, datum_objave);
sta kaze explain:
Code:
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: v
type: range
possible_keys: PRIMARY,datum_objave,kategorija,id
key: datum_objave
key_len: 9
ref: NULL
rows: 14185
Extra: Using where; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: k
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: es.v.kategorija
rows: 1
Extra: Using where; Using index
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: a
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: es.v.autor
rows: 1
Extra:
3 rows in set (0.00 sec)
kaze da vidi nas key (id) ali nece da ga koristi ... aj da vidimo ocel biti nesto brze ako mu ga damo po forsiranju
Code:
mysql> explain SELECT SQL_NO_CACHE v.id, v.naslov, a.ime_prezime, v.mala_slika, v.uvod FROM vijesti v force key (id), kategorije k, administratori a WHERE k.kategorija_id = v.kategorija AND v.autor = a.id AND v.tip =1 AND v.media =0 AND v.pozicija IN ( '5', '6', '7', '8', '9', '1000' ) AND k.parent_id IN ( 4, 5 ) AND v.id NOT IN ( 22083, 22075, 22077, 22070 ) AND v.datum_objave <= '2009-08-13 10:58:00' ORDER BY v.pozicija, v.datum_objave DESC LIMIT 5\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: v
type: range
possible_keys: id
key: id
key_len: 4
ref: NULL
rows: 20005
Extra: Using where; Using filesort
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: k
type: ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: es.v.kategorija
rows: 1
Extra: Using where; Using index
*************************** 3. row ***************************
id: 1
select_type: SIMPLE
table: a
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: es.v.autor
rows: 1
Extra:
3 rows in set (0.02 sec)
super .. force radi, znamo sintaksu :D .. ali da li ima razlike u brzini:
ima .. malopre je bio 0.3 sad je 0.36-0.4 .. znaci sporo ... da probamo malo da promenimo redosled ...
Code:
alter table vijesti add key kljuc2 (datum_objave, id, autor, tip, media, pozicija);
SELECT SQL_NO_CACHE v.id, v.naslov, a.ime_prezime, v.mala_slika, v.uvod FROM vijesti v force key (kljuc2), kategorije k, administratori a WHERE k.kategorija_id = v.kategorija AND v.autor = a.id AND v.tip =1 AND v.media =0 AND v.pozicija IN ( '5', '6', '7', '8', '9', '1000' ) AND k.parent_id IN ( 4, 5 ) AND v.id NOT IN ( 22083, 22075, 22077, 22070 ) AND v.datum_objave <= '2009-08-13 10:58:00' ORDER BY v.pozicija, v.datum_objave DESC LIMIT 5\G
tu smo negde na 0.3 - 0.33 ...
sada tu mozes da se igras .. realno zgodno je da tu imas kljuc koji za prvo polje ima EQ koji najvise filtrira tabelu (dakle na primer ako ce tip da izbaci 90% kolona iz rezultata - njega stavis prvog ... range polja stavis poslednja - kao na primer ovaj datum) ..
elem .. imamo ga sada (bez hintova i bez ovog velikog kljuca) na 0.3 ... (jos uvek smo sa temp diskom na /tmp) ... sve to isto samo tmpdir stavimo na ram disk i brzina je: 0.09s