Še počasen query, če je limit velik

3 naročniki

Tale query z LIMIT 0, 21 se izvaja 0.001sekunde

SELECT n.news_id, n.title, n.news_id, n.short, n.picture, n.youtube, n.audio, n.gallery_id, n.inserted, u.url
FROM news AS n
JOIN news2category AS n2c
FORCE INDEX FOR JOIN (
news_id
) ON n2c.news_id = n.news_id
JOIN url AS u ON n.news_id = u.action_id
WHERE n2c.category_id =2
AND u.action = 'viewNews'
AND n.inserted < NOW( )
ORDER BY n.inserted DESC
LIMIT 0 , 21

Explain:

id     select_type     table     type     possible_keys     key     key_len     ref     rows     Extra
1     SIMPLE     n     index     PRIMARY     inserted     8     NULL     21     Using where
1     SIMPLE     u     ref     action_id     action_id     5     baza.n.news_id     1     Using where
1     SIMPLE     n2c     ref     news_id     news_id     5     baza.n.news_id     1     Using where

Isti query z LIMIT 6000, 21 se izvaja 0.6 sekunde.
Explain:

id     select_type     table     type     possible_keys     key     key_len     ref     rows     Extra
1     SIMPLE     n     ALL     PRIMARY     NULL     NULL     NULL     19182     Using where; Using filesort
1     SIMPLE     u     ref     action_id     action_id     5     baza.n.news_id     1     Using where
1     SIMPLE     n2c     ref     news_id     news_id     5     baza.n.news_id     1     Using where

Če odstranim FORCE INDEX FOR JOIN(news_id) pa naredi query z LIMIT 6000,21 polovico hitreje, v 0.3s. Čeprav explain pokaže "Using temporary"
Explain:

id     select_type     table     type     possible_keys     key     key_len     ref     rows     Extra
1     SIMPLE     n2c     ref     news_id,category_id     category_id     5     const     5360     Using where; Using temporary; Using filesort
1     SIMPLE     n     eq_ref     PRIMARY     PRIMARY     4     baza.n2c.news_id     1     Using where
1     SIMPLE     u     ref     action_id     action_id     5     baza.n.news_id     1     Using where

Kako bi lahko pospešil izvajanje querijev z višjimi limiti?
Ena ideja je, da bi preverjal LIMIT > COUNT/2 potem nebi forcal indexa - news_id ampak verjetno to ni najboljše.

Kakšna ideja?

Ne bo enostavno, po moje. Pri uporabi visokih vrednostih za LIMIT največ profitiraš z indeksom na ORDER BY polju, ker mora MySQL za zapise 6000 - 6021 najprej vedeti vrstni red vseh zapisov do vključno 6021, da lahko potem preskoči 6000 zapisov.

Tole s preverjanjem LIMIT > COUNT/2 je samo ugibanje, ne moreš predvideti, kako se bo stvar obnesla, ko se bo število zapisov spreminjalo. In načeloma MySQL v večini primerov bolje izbere indeks, kot ti, zato bi jaz FORCE INDEX uporabil le, če bi bil res kje velik problem z determinističnim in konstantnim rezultatom (kar tvoj očitno ni, saj se bolje obnese brez njega).

Kako dolgo se ti izvaja LIMIT 0, 21 brez FORCE INDEX? Če je to v znosnem rangu, odstrani to in pusti, da MySQL naredi, kar zna. Drugače pa spremeni pristop (kakšne vmesne predizračunane tabele? - ne poznam problema).

fatg: brez forcanega indexa se LIMIT 0,21 izvaja 250x počasneje ~ 0.25 sekunde

Sem probal odstraniti joinano tabelo 'url' pa se profitira le 0.05s
Kaj drugega 'predizračunanega' se pa ne morem domisliti, saj gre le za enostavne novice, ki so v več kategorijah.

Kolikokrat pa se realno izvajata LIMIT 0,21 in 6000,21? Serviraš to obiskovalcem, ali v backendu? Če imaš poizvedb za 6000,21 samo par sto na dan, potem ne izgubljaj več časa s tem.

Pomojem te strani odpira samo kak google-bot in kak user, če 'zaide' pregloboko.
Pravzaprav se to zgodi pri pagingu novic, ko kdo klikne na "zadnja stran". To sem pa ugotovil čisto slučajno, z logiranjem mysql querijev počasnejših od 0.5sekunde.

Saj zadeva ni problematična, ampak me firbec matra kako najbolje(s hitrimi poizvedbami) narediti paging npr 30k kategoriziranih novic urejenih po datumu.

še primeri različnih limitov s FORCE INDEX:
LIMIT 0,21 ~ 0.002s
LIMIT 500,21 ~ 0.02s
LIMIT 1000,21 ~ 0.04s
LIMIT 1500,21 ~ 0.08s
LIMIT 2000,21 ~ 0.20s
LIMIT 3000,21 ~ 0.32s
LIMIT 4000,21 ~ 0.41s
LIMIT 5000,21 ~ 0.52s
LIMIT 6000,21 ~ 0.61s

brez FORCE INDEX:
LIMIT 0,21 ~ 0.26s
LIMIT 500,21 ~ 0.25s
LIMIT 1000,21 ~ 0.26s
LIMIT 1500,21 ~ 0.26s
LIMIT 2000,21 ~ 0.27s
LIMIT 3000,21 ~ 0.27s
LIMIT 4000,21 ~ 0.29s
LIMIT 5000,21 ~ 0.29s
LIMIT 6000,21 ~ 0.29s

Jaz se ne bi sekiral zaradi teh časov. Važno, da so pogosti queryji hitri. Si pri meritvah upošteval query cache?

Ena opcija za paging je predpripravljena flat tabela, kamor filaš podatke iz tega queryja, s tem da zavržeš določene specifične pogoje (categoryid), obdržiš pa preverbo na insertedat in action. V njej hraniš samo polja, nujna za prikaz na seznamih. Potem pa po njej za prikaz queryjaš po categoryid in ORDER BY insertedat. Sicer ohraniš ta glavni požrešni del (LIMIT), tako da je vprašanje, koliko pridobiš, se pa znebiš joinov pa še indekse imaš lahko prilagojene branju (torej recimo samo 1 sestavljen index na insertedat + categoryid). Čim manj indeksov, toliko bolje tudi za pisanje, pri branju pa MySQL itak uporabi samo 1 indeks na posamezni tabeli.

Tabelo generiraš tako, da query v ozadju izvajaš npr. 1x na pet minut. Na ta način profitiraš še s query cacheom, ki se drugaže izprazni ob vsaki spremembi tabele (tukaj ti drži do naslednjega rebuilda). Če pa nimaš veliko podatkov, lahko "rezerviraš" npr. 100mb in narediš to tabelo MEMORY, tako da jo ima strežnik kar v ramu. Potreba po indeksih odpade, pa še queryji so super hitri.

fatg: ti več kot očitno obvladaš te zadeve:) Priporočaš kakšno knjigo na to temo ? Nekaj kar se bere lahko, ne nek reference s 500 stranmi :)

Da, query cache sem upošteval.

Najlepša hvala za obširne odgovore in nasvete!
Zaenkrat sem ti 'prilimal' toliko plusov kolikor se je dalo v tej temi.
Ko te pa naslednjič srečam v živo, te pocukam za rokav in povabim na:
while(pivo) pivo++

Hvala in LP

@SpinX: to znanje je bolj rezultat neprespanih noči, kupa člankov z neta in MySQL dokumentacije, kot knjig :D. Knjige sem prebral samo kakšne osnovne, je pa High Performance MySQL: Optimization, Backups, Replication, and More baje zelo solidna in je tudi na mojem spisku. :)

blackmamba: se priporočam. :)