Mysql CACHE dotazu – MySQL – Fórum – Programujte.com

Zdravím, v tabulce naměřených hodnot s cca 260tisíc řádky potřebuji udělat sumu jednoho sloupce. Dotaz mi trvá cca 0,3 vteřiny, což je pro mě dlouhý čas, v případě interpretace několika sum na web. Napadaj mi dvě řešení:

1. Sloupec hodnot, který je řešen inkrementálně převést na sumu. Celková suma dosahuje velikosti 0-1000 s přesností na desetiny. Pak bych snadno udělal sumu jako rozdíl dvou dat. Musel bych ale přepočítat všechny již uložené řádky.

2. Vytvořit cachovací tabulku, do které bych vždy sumu vložil i s časem posledního záznamu. Při dalším dotazu bych sumu vyčetl a spočítal sumu několika desítek nově vytvořených řádků. Toto by bylo určitě jednodušší řešení na implementaci.

Jaké řešení by jste mi doporučily?

mas na tom stlpci index?

#2 Flowy
spis bych pochyboval o tom, ze by se index pro sumu pouzil.. mozna tak pokud by delal vyber casti radku podle toho scitaneho sloupce.

Jinak treti moznost je mit trigger, ktery by updatoval nejakou konkretni hodnotu v "cache" tabulce

#1 Pavelv
Nejsem sice žena, ale doporučím ti trigger.

Nebylo by jednodusi pri ukladani do tabulky pocitat zrovna i tu sumu v jine tabulce dalsim dotazem?
Nejak takto: INSERT INTO tabulka sloupec45 VALUES sloupec45+123

#5 peter
Však proto jsme mu navrhli ten trigger. Ty bys to chtěl řešit oklikou přes aplikaci? To by bylo poněkud nepraktické a pomalé.

#2 Flowy
Index samozřejmě mám, ale nepociťuji žádné zrychlení.

#3 KIIV
Díky, také jako nejlepší se mi jeví použít trigger při ukládání dat.

Zkus pouzit select high priority ..., teoreticky by to mohlo neco ubrat. Posli jeste select, urco se to da vytunit hintama.

A jeste posli exekucni plan pro tvuj selekt, mrknu jak jsi na tom s rychlosti celkoveho selektu, jestli tam nemas nejake krpy.

#8 Mixxy
select vypadá takto: 

SELECT SUM(data23) FROM mereni WHERE YEAR(cas) = YEAR(CURDATE())

high priority mi nic neurychlí

A posli jeste vysledek tohoto dotazu:

explain SELECT SUM(data23) FROM mereni WHERE YEAR(cas) = YEAR(CURDATE());
 

A strukturu tabulky :-)

Předpokládam, že nezáleží na struktuře nezúčastněných sloupců. Formát sloupce cas je datetime s primárním klíčem, sloupec data23 je typu float(5,1) s klíčem MUL.

explain mi dá select_type = SIMPLE, table = mereni, type = index, possible_keys = null, key = idx_mereni_data23, key_len = 5, ref = null, rows = 302395, Extra = Using where, Using index

Ok, diky za informace, zkusim si to nasimulovat a zkusim ti vymyslet neco, co by ti ten dotaz zrychlilo.

timdle: 
YEAR(cas)
se zbavis moznosti pouzit index nad tim sloupcem... v posgresql by slo udelat index pres  YEAR(cas), ale mysql to asi nebude umet

takze minimalne doporucuju prekopat aspon na interval od zacatku roku do konce roku - aby se aspon ten index pouzit mohl  (pokud tam mas ted jen letosni data, tak se to moc neprojevi ale pockej za par let az tam budou desitky milionu zaznamu)

#8 Mixxy
Jenom blázni dávají high priority na pomalé dotazy.

To Kit: Na druhou stranu nevis nic o architekture a vytizenosti te databazi. Pokud by Jefry sbiral tuny dat a sqlko by mel pomaly jen kvuli vytizenosti databaze, mozna by mu to trochu pomohlo a to bylo v mem ocekavani...

#17 Mixxy
High priority na select znamená dát low priority na vše ostatní. Tím odstavíš všechny inserty a updaty. Procesy, které na ně čekají, uvězníš v paměti nebo ve swapu.

#15 KIIV
Takže jestli to chápu správně, abych mohl uplatnit index, nesmím daný sloupec prohánět funkcí? Jak tedy získam časové razítko začátku roku?

#19 Pavelv
neni problem pouzit neco jako:  

cas >= MAKEDATE(YEAR(NOW()),1)

hlavne se da pouzit ten index

Kazdopadne v predchozi praci sem treba zjistoval, proc rotace tabulek v mysql byly tak priserne pomaly.. sice byl index na tabulce, kde se pouzival unixtime, ale blbec predchozi kolega vyblil neco takovyho  select * from table where from_unixtime(ts) >= 20100101000000 ...

to mozna bezi kdyz je tam par zaznamu, ale nad desitkama milionu se to uz zatracene protahne, prepocitavat pro kazdy radek ts na datetime a pak to teprve porovnat s konstantou

Ok, je třeba nad tim přemýšlet, a ne jen tupě psát. Vymyslel jsem takovýto kus kódu. Moc se mi nelíbí ten select dělat třikrát nad stejnýma daty. Nešlo by to nějak upravit? 

create trigger tr_update_cache
after insert on mereni
for each row
begin
	UPDATE cache c  
		JOIN (SELECT sum(data23) as suma_year
			FROM mereni WHERE cas >  CONCAT(YEAR(CURDATE()),'-01-01 0:0:0')) d
		SET c.value = Coalesce(d.suma_year,0)
		WHERE name = 'data23_year';

		UPDATE cache c  
		JOIN (SELECT sum(data23) as suma_month
			FROM mereni WHERE cas >  CONCAT(YEAR(CURDATE()),'-',MONTH(CURDATE()),'-01 0:0:0'))d
		SET c.value = Coalesce(d.suma_month,0)
		WHERE name = 'data23_month';

		UPDATE cache c  
		JOIN (SELECT sum(data23) as suma_day
			FROM mereni WHERE cas >  Date_add(now(), INTERVAL -1 DAY))d
		SET c.value = Coalesce(d.suma_day,0)
		WHERE name = 'data23_day';
end

Nakonec i na ten začátek roku jsem vymyslel funkci, tak nevím co by bylo lepší

uf, ted si z kazdyho insertu vyrobil jeste 3x podobnej dotaz jako pred tim... tim si pasoval trigger na "nic se tu nedeje a server je furt pretizenej na 2 roky dopredu"

ten princip by byl JEN pricist pridanou hodnotu do tabulky cache na spravne misto, ne to prepocitavat porad dokola

#22 KIIV
Ano, ale uvědomil jsem si, že potřebuji v té tabulce hodnotu za poslední rok, měsíc, den. proto to přepočítávání

Jestli by treba nebylo jednodussi podivat se, jak to pocitaji ruzne statistiky pristupu. U nas pouzivaji Aw stat nebo Zabbix. Zabbix umi sledovat i teplotu a umi delat grafy pro ruzna casova obdobi. Vetsinou vyuzivame pro teplotu v severovni a zda konkretni pc a zarizeni pingaji.

#23 Pavelv
na to ti ale staci jen suma po dnech - kdyz das do triggeru neco jako:

insert into cache (datum, suma) values (date(now()), sum23) on duplicate key set suma=suma+sum23;

tak se jen pripocte k predchozi sume - nepotrebujes prepocitavat co tam je, kdyz uz to spocitany mas N-krat

a secist tech 365 dennich hodnot za rok se vcelku da prezit

(je samozreme nutnost mit unikatni klic nad datumem)