SQL dotaz s limitem, pri vetsim poctu $start radku se z

SQL dotaz s limitem, pri vetsim poctu $start radku se zpomal

Zdar ted si nejsem jistej ale nemelo by byt SQL dotazu jedno jestli bude limit


LIMIT 0,50

nebo

LIMIT 5000,50

? Pokazde vypise stejny pocet radku, jde jen o to kolik jich preskoci.

Ja jen ze generovaci cas v prvnim pripade je 0,002s a v druhem 0,3s

Nechci videt az se mi tipovacka rozroste na desitiky tisice tipu :(( ( Momentalne cca 8000,- po 3 mesicich provozu )

SELECT h1.prezdivka as jmeno_hrace1, h2.prezdivka as jmeno_hrace2, v.schvaleno as schvaleno,
v.kolo, v.d_tym, v.h_tym, h.prezdivka, ts.nazev_souteze as soutez, v.id_souteze , v.d_hrac, v.h_hrac,
date_format(vs.datum_tipu,'%d.%m. %H:%i') as datum,
vs.id_zapasu, vs.id_hrace,
if(vs.odehrano='1',vs.body,'?') as body,
if(v.schvaleno='2',v.d_goly,'-') as g1,
if(v.schvaleno='2',v.h_goly,'-') as g2,
vs.g1 as g1tip, vs.g2 as g2tip,
t1.logo_tymu_small as logo1, t2.logo_tymu_small as logo2, t1.jmeno_tymu as jmeno_tymu1, t2.jmeno_tymu as jmeno_tymu2
from vysledky_tipovacka as vs
JOIN vysledky as v ON v.id_zapasu=vs.id_zapasu
JOIN tymy as t1 ON v.d_tym=t1.id_tymu
JOIN tymy as t2 ON v.h_tym=t2.id_tymu
JOIN hraci h ON h.id=vs.id_hrace
JOIN typ_souteze ts ON v.id_souteze=ts.id_souteze
JOIN hraci h1 ON v.d_hrac=h1.id
JOIN hraci h2 ON v.h_hrac=h2.id
order by id_radku DESC
LIMIT $a, $b



nevim no , na tomto dotazu neni nic slozite, nic narocneho, je imho rychly .. jdu si udelat test ( strankovat jednodduchou tabulku o 1000000 radcich bez JOINOVANI) ...kdyby mel nekdo napad proc to blbne ...

Ano samozrejme, spojovaci sloupce tabulek ktere JOINUJU jsou primarni anebo cizi klice s indexem.
Nevim co s tim ... :/

ano

viz dalsi thread ... http://interforum.interval.cz/ShowPost.aspx?PostID=32494

explain -> http://pesonline.info/__explain__.gif

Uz jsem ho doplnil, doba je temer totozna stale to haze vysoke cislo generovani SQL ( tedy vysoke... 0,29s pri limtu 7500,50).

Lock this, vyreseno.

pochybuju ze by se nekdo vyznal v tomhle SQL krom tebe, kajmana a par dalsich vyvolenych (neapeluji na znalost, ale je fakt ze to neni SQL snadno predstavitelny pro lidi co tahaj data z 1 ci 2 tabulek a navic to jeste neni jejich web )

ale budiz

pokud

by idcka sli po sobe ( v mem pripade tipy nikdy nemazu tj -> kontinualni rada)

select @max_id := max(id_riadku) from vysledky_tipovacka

SELECT 

 h1.prezdivka as jmeno_hrace1, 

 h2.prezdivka as jmeno_hrace2, 

 v.schvaleno as schvaleno, 

 v.kolo, 

 v.d_tym, 

 v.h_tym, 

 h.prezdivka, 

 ts.nazev_souteze as soutez, 

 v.id_souteze , 

 v.d_hrac, 

 v.h_hrac, 

 date_format(vs.datum_tipu,'%d.%m. %H:%i') as datum, 

 vs.id_zapasu, 

 vs.id_hrace, 

 if(vs.odehrano='1',vs.body,'?') as body, 

 if(v.schvaleno='2',v.d_goly,'-') as g1, 

 if(v.schvaleno='2',v.h_goly,'-') as g2, 

 vs.g1 as g1tip, 

 vs.g2 as g2tip, 

 t1.logo_tymu_small as logo1, 

 t2.logo_tymu_small as logo2, 

 t1.jmeno_tymu as jmeno_tymu1, 

 t2.jmeno_tymu as jmeno_tymu2 

from vysledky_tipovacka as vs 

JOIN vysledky as v ON v.id_zapasu=vs.id_zapasu 

JOIN tymy as t1 ON v.d_tym=t1.id_tymu 

JOIN tymy as t2 ON v.h_tym=t2.id_tymu 

JOIN hraci h ON h.id=vs.id_hrace 

JOIN typ_souteze ts ON v.id_souteze=ts.id_souteze 

JOIN hraci h1 ON v.d_hrac=h1.id 

JOIN hraci h2 ON v.h_hrac=h2.id 

where id_radku > @max_id-$a-$b and id_radku < @max_id-$a

order by id_radku DESC 

Mno a jde to vyresit i SUBSELECTEM pokud by to kontinualni rada nebyla. a to sem pastovat nebudu, Vy zkusenejsi si to domyslite ;)