Nejčtenější za poslední týden

Školení, která pořádám

Pokud chceme zobrazovat nejčtenější články všech dob, není to nic složitého. Stačí do tabulky článků přidat sloupec čtenost a aktualizovat ho při každém zobrazení článku (s MyISAM tabulkami je vhodné použít UPDATE LOW_PRIORITY). Pokud nad tento sloupec přidáme index, tak se podle něj bude i rychle řadit.

Se zobrazením článků za pevné období (např. po dnech) také není problém. Stačí vytvořit tabulku clanky_ctenost (clanek, datum, ctenost) s unikátním klíčem nad (clanek, datum) a aktualizovat ji pomocí ON DUPLICATE KEY UPDATE. Při existenci indexu nad (datum, ctenost) bude získání nejčtenějších článků v konkrétním dni opět rychlé.

Složitější je získání nejčtenějších článků za nějaké plovoucí období (např. za posledních 7 dní). K tomu samozřejmě také můžeme použít předchozí tabulku, problém je ale ten, že pro dotaz WHERE datum >= CURDATE() - INTERVAL 7 DAY GROUP BY clanek ORDER BY SUM(ctenost) DESC LIMIT 10 nelze vytvořit index, takže pokud je článků hodně, bude tento dotaz pomalý.

Můžeme samozřejmě udělat to, že do tabulky článků přidáme sloupec ctenost_tyden, který budeme aktualizovat spolu s tabulkou clanky_ctenost při každém přístupu a z kterého jednou denně odečteme osm dní staré přístupy. V MySQL 5.1 k tomu lze použít podporu pravidelných událostí, se staršími verzemi je nutné použít cron.

Nevýhoda tohoto řešení spočívá v tom, že jednak závisí na pravidelném provádění nějakého úkonu a jednak potřebuje tabulku clanky_ctenost. Přemýšlel jsem proto o nějakém elegantnějším řešení. To lze použít v případě, že nás nezajímá čtenost přesně za poslední týden, ale stačí nám poněkud vágnější „poslední dobou“.

Myšlenka řešení spočívá v tom, že čím novější přístup, tím za něj započteme víc bodů. Konkrétně třeba takovýmto vzorcem: pow(1.2, time() / 60 / 60 / 24 - 49 * 365). Pokud o tuto hodnotu zvýšíme popularitu článku při každém přístupu, tak bude vyjadřovat jeho oblíbenost v poslední době – čím novější přístup, tím hodnotnější. Např. 100 přístupů dnes má stejnou hodnotu jako 358 přístupů před týdnem nebo 23738 přístupů před měsícem. Hodnota 1.2 vyjadřuje, jak rychle chceme čtenost zastarávat, magická konstanta 49 udržuje číslo v rozsahu povoleném v PHP a v MySQL u datového typu double (od E-308 do E+308). Při zvýšení hodnoty 1.2 by bylo potřeba tuto konstantu změnit a vzorec čas od času oprášit (v této podobě vydrží asi 20 let).

<?php
$popularita = pow(1.2, time() / 60 / 60 / 24 - 49 * 365);
mysql_query("UPDATE LOW_PRIORITY clanky SET nedavna_popularita = nedavna_popularita + $popularita WHERE id = $id");
?>
Jakub Vrána, Řešení problému, 6.4.2009, diskuse: 9 (nové: 0)

Diskuse

LesTR:

Zajimavé řešení!
Jen mám trochu strach, že použitím takto často aktualizovaného sloupce, pošlu do kytek query cache mysql. Pletu se?

ikona Jakub Vrána OpenID:

Ano, query cache tabulky clanky se bude stále promazávat. Místo ní lze použít inteligentnější aplikační cache s expirací.

Kalda:

Možná se pletu, ale nestane se v PHP při použití funkce mysql_query při UPDATE LOW_PRIORITY to, že PHP bude čekat na vykonání SQL příkazu?

Sám to řeším tak, že mám pouze HEAP tabulku s počítadlem v a tu v pravidelných intervalech převádím do tabulky původní (přičtu a vynuluji), práce s tím se znatelně zrychlila.

ikona Jakub Vrána OpenID:

Je to tak, PHP čeká na dokončení příkazu. Ukládání do pomocné tabulky a občasné přesypání je samozřejmě lepší (už jen kvůli méně časté aktualizaci indexu), ale pracnější.

Kajman:

A nebylo by lepší to odečítat? Pak by se teprve dal plně využít index na řazení a výběr prvních deseti. MySQL zatím desc indexy neumí.

ikona Jakub Vrána OpenID:

Fyzické ukládání indexů v DESC pořadí MySQL skutečně nepodporuje, ale normální indexy odzadu samozřejmě číst umí.

Kajman:

Ale, když nechci normální, ale např. index na dvou sloupcích (kategorie,nedavna_popularita), tak tam už to při "where kategorie=1 order by nedavna_popularita desc" tenhle index pro řazení nepoužije, ale při asc by ho mysql použila. Ale možná se jen pletu a mysql podceňuji :-)

ikona Jakub Vrána OpenID:

Pleteš se, MySQL tento index použije (ověřeno).

Student:

Ak by som chcel zmeniť rýchlosť zastarávania (hodnotu 1.2), ako vypočítam hodnotu novej magickej konštanty?

Poprípadne ako by som vedel upraviť celý ten vzorec, aby to bolo citlivé na hodiny a nie dni? Celkom by ma teda zaujímal proces tvorby toho vzorca.

Vložit komentář

Používejte diakritiku. Vstup se chápe jako čistý text, ale URL budou převedeny na odkazy a PHP kód uzavřený do <?php ?> bude zvýrazněn. Pokud máte dotaz, který nesouvisí s článkem, zkuste raději diskusi o PHP, zde se odpovědi pravděpodobně nedočkáte.

Jméno: URL:

avatar © 2005-2018 Jakub Vrána. Publikované texty můžete přetiskovat pouze se svolením autora. Ukázky kódu smíte používat s uvedením autora a URL tohoto webu bez dalších omezení Creative Commons. Můžeme si tykat. Skripty předpokládají nastavení: magic_quotes_gpc=Off, magic_quotes_runtime=Off, error_reporting=E_ALL & ~E_NOTICE a očekávají předchozí zavolání mysql_set_charset. Skripty by měly být funkční v PHP >= 4.3 a PHP >= 5.0.