Diskuze: Paginace a filtrace

maxijoey

Člen

maxijoey:31.10.2019 10:03

Po třech dnech zoufalých pokusů se jdu zeptat. Mám tabulku v ní cca 160 000 záznamů. Potřebuji vybírat záznamy dle některých sloupců, tedy filtrovat a také stránkovat, to jest mám stránky po 50 v aplikaci a zobrazit třeba stránku 150 s filtrovanými výsledky.

Zkusil jsem: Samozřejmě jsem zkoušel různé experimenty s indexy, včetně složených indexů. Také jsem zkusil pro stránkování nevyužít LIMIT Offset jelikož čím dále vybírá tím je pomalejší, ale to má jisté nevýhody při filtrování, které nevím jak odstranit. Rovněž subdotaz kde si vyberu nejdříve požadovaných 50 záznamů z požadovaného umístění a na něj aplikovat vše ostatní, ale to už z principu nedělá co má viz nepoužití Offsetu, leč je to rychlé pak.

Chci docílit: Snažím se dosáhnout kombinace těchto dvou požadavků v rozumném čase. Momentálně to trvá klidně půl minuty což je neúnosné. Jde to vůbec nějak při tabulce těchto rozměrů. Lze toho vůbec nějak rozumně dosáhnout?

Odpovědět

31.10.2019 10:03

maxijoey

Člen

maxijoey:31.10.2019 10:06

Ještě bych doplnil že data potřebuji groupovat což není tak hrozné ale taky seřadit což je tam vražda docela

Nahoru Odpovědět

31.10.2019 10:06

Peter Mlich

Člen

Peter Mlich:31.10.2019 11:10

To je takovy teoreticky dotaz. Takze zkus google.

SELECT expressions
FROM tables
[WHERE conditions]
[ORDER BY expression [ ASC | DESC ]]
LIMIT number_rows [ OFFSET offset_value ];

Strankovani
SELECT ... LIMIT 1, 10 --- 1. radek a 10 dalsich
SELECT ... LIMIT 10, 10 --- 10. radek a 10 dalsich
SELECT ... LIMIT 10, 50 --- 10. radek a 50 dalsich (50 na stranku)

Serazovani
SELECT ... ORDER BY sloupec1 ASC, sloupec2 DESC

Serazovani a strankovani
SELECT ... ORDER BY sloupec1 ASC, sloupec2 DESC LIMIT 10, 50

Vyber 50 a serazeni
SELECT * FROM (SELECT ... LIMIT 10, 50 ) ORDER BY sloupec1 ASC, sloupec2 DESC

Serazeni grupy sloupce

SELECT GROUP_CONCAT( `bb`.`zkratka` ORDER BY `bb`.`zkratka` ASC SEPARATOR ', ') AS `soucast`
FROM `s_formgroup` `xx`
  LEFT JOIN `s_soucast` `bb` ON `bb`.`idsoucast`=`xx`.`id2`
WHERE `xx`.`id1`=`a`.`idform` AND `xx`.`ap`=10200
GROUP BY `xx`.`id1`

Az budes vedet, co presne chces, tak dej vedet. Nejake demicko tabulky (10 radku), jak by mel vypadat vysledek (2 radky) a tak.

Casty problem dotazu je, ze lide propojuji, co porpojovat netreba a pak vznikaji tabulky 100 x 160.000, a pak je to pomale.
A druhy casty problem je, ze mas v tabulkach indexy, ale pri urcitych operacich vytvaris nove tabulky uz bez indexu. Cokoliv pak nad tim delas, muze byt vyrazne pomalejsi Cili, bys mel vybrat nejdriv co nejvic jde pomoci indexu a az pak nad tim delat dalsi operace.
A treti casty problem je, ze tam nemas indexy nebo je nemas spravne

Editováno 31.10.2019 11:10

Nahoru Odpovědět

31.10.2019 11:10

maxijoey

Člen

maxijoey:31.10.2019 11:18

Mno tohle všechno už jsem tak nějak zkoušel. To stránkování co jsi psal samozřejmě znám. A to je i co jsem psal že je pomale. Ono to bude pomalé i když ten dotaz nebude nic jinýho dělat. Respektive takto: LIMIT 10, 10 bude rychlý vždy. LIMIT 150000,10 mi trvá třeba i 12 sekund. On totiž vybere vždy vše až k limitu a co nepotřebuje tak zahodí.
Co ale neznám je GROUP_CONCAT, co to přesně dělá prosím? Jinak ano zkusím sem hodit nějaká konkrétnější data.

Nahoru Odpovědět

31.10.2019 11:18

Peter Mlich

Člen

Peter Mlich:31.10.2019 12:54

To nemas mysql, ze ne? To chovani, co popisujes mi pripomina spis mssql nebo oracle, tam se to resi prave tim offsetem.
Jak rikam, muzeme se tu bavit teoreticky, nebo sem das sql dotaz.

Editováno 31.10.2019 12:55

Nahoru Odpovědět

31.10.2019 12:54

Peter Mlich

Člen

Peter Mlich:31.10.2019 13:06

Nemusis data, ale s datama by to bylo asi lepsi. Zkus treba ten sql dotaz, co je pomaly. Ono, 2s u komplikovaneho dotazu nad 160.000 radky, to by i mohlo byt. Ikdyz je to neprijemne.

Ja pouzivam mysql, jestli mas neco jineho, ty funkce se muzou jinak jmenovat nebo resit nejakou oklikou.

CONCAT (sloupec1, sloupec2, sloupec3) -- spoji sloupce do jednoho sloupce
GROUP_CONCAT (sloupec1) -- spoji 1 sloupec pro vsechny zgrupovane radky
COUNT(sloupec) -- pocet zgrupovanych radku
SUM(sloupec) -- suma hodnot sloupce pro vsechny zgrupovanych radky

Pr
sloupec1, sloupec2, sloupec3
aaa, bbb, ccc
11, 22, 33
44, 55, 33
CONCAT( sloupec1, sloupec2, sloupec3)
CONCAT = aaabbbccc
CONCAT = 112233
... GROUP_CONCAT (sloupec1) ... GROUP BY sloupec3
GROUP_CONCAT = ccc
GROUP_CONCAT = 11,44
A protoze u group concatu bys to nemel serazene, tak ma jeste moznost to vnitrne seradit. Coz se mi skvele hodi u te soucasti, kde potrebuji seznam oddeleni seradit pred tim, nez je spoji carkou. Uvadim to proto, ze by to mozna mohlo vyresit castecne tvuj problem. Zalezi na tom, co ma byt vysledkem...

Editováno 31.10.2019 13:06

Nahoru Odpovědět

31.10.2019 13:06

maxijoey

Člen

maxijoey:31.10.2019 17:25

Díky za vysvětlení. Jinak hele nee, je to MySql konkrétně Mária a je to její chování například při tomto dotazu:
SELECT c.id_product FROM production_products AS c WHERE 1 GROUP BY c.pair_code LIMIT 162500, 50
Regulérně 12 vteřin když chceš prostě ke konci výsledky. Jakmile offset mezíš třeba na polovinu je to zas relativně hned.
Jinak teda povedlo se mi ted vytvořit pár složených indexů při kterých když začnu filtrovat tak se i toto chování upraví na dobré výsledky, ale zatím testuji.
Je to teda tak že jeden velký dotaz co mi tahal všechna potřebná data jsem rozdělil na dva. První dotaz mi vytáhne id položek které chci. To je nyní pod půl vteřiny průměrně a druhý dotaz dle těch id vytahá data k těm řádkům ke všem to je také pod vteřinu.
Je to zvláštní celkem ale zdá se funkční. Ještě teda uvidíme až tam začnu trochu joinovat ale..

Nahoru Odpovědět

31.10.2019 17:25

maxijoey

Člen

maxijoey:31.10.2019 17:30

Ještě mám jeden dotaz trochu pro upřesnění. U složeného indexu, jde o pořadí aby se dal využívat zleva i jedna jeho část. Ale jde o pořadí toho jak sloupce do něj vložím nebo o pořadí sloupců v tabulce a to do něj reflektovat?

Nahoru Odpovědět

31.10.2019 17:30

maxijoey

Člen

maxijoey:31.10.2019 17:32

Jinak na tu paginaci doporučují to dělat tak že si pamatuješ id kde jsi skončil a od toho odečítat. To je pak rychlé tedy konstatní, ale nevyfiltruješ nic prakticky.

Nahoru Odpovědět

31.10.2019 17:32

Peter Mlich

Člen

Peter Mlich:1.11.2019 8:24

Nemivam s LIMIT problem. Ale tez nemam 160.000 zaznamu na testovani, takze, kdo vi.
Tak sem dej cely ten dotaz.
Muzes skladat dva dotazy do sebe, takhle nejam by to mohlo jit:
SELECT ... FROM (SELECT id FROM tabulka ...) AS tab2 LEFT JOIN tabulka
SELECT ... FROM tabulka WHERE id IN (SELECT id FROM tabulka ...) AS tab2
Tusim je tam nutne dat alias (AS).

Nahoru Odpovědět

1.11.2019 8:24

maxijoey

Člen

maxijoey:1.11.2019 8:38

SELECT c.*, ip.price AS suppPrice, (SELECT COUNT(id_product) FROM production_products WHERE pair_code = c.pair_code) AS countVar,
(SELECT SUM(GREATEST(SIGN(stock),0) * stock) FROM production_products
WHERE pair_code = c.pair_code) AS stockSum
FROM production_products AS c
LEFT JOIN import_products AS ip ON ip.id_supplier = c.id_supplier AND ip.code = c.code
WHERE c.id_supplier = 1 AND c.state = 2 GROUP BY pair_code ORDER BY c.id_product LIMIT 47500, 50

to já tu používám takový podobný srandy :-) Jsem za poslední tři dny vyzkoušel věcí až bych brečel :-D ale výsledku už jsem se asi dobral. Taky jsem zjistil že se mi nějak jako zanáší linux server. Za tři dny je jeho odezva pekelná, po restartu to funguje vše i na původním kodu dobře. Ale jak to mám ted tak bych řekl že i líp nakonec.

Nahoru Odpovědět

1.11.2019 8:38