pivot table ātrdarbība uz daudz ierakstiem

[Val]

Labvakar,

izdomāju uzrakstīt, varbūt pametīsiet kādu ideju.

 

Doma tāda. Mysql tabula, kurā krīt iekšā statistikas dati. Value, filter kolonnas šajā gadījumā nav vajadzīgas, jo tiks izmantotas citur.

 

Tabulas struktūra un testa dati. Reālajā vidē tie būs daudz, daudz vairāk un sāksies problēmas ar summēšanu, tāpēc arī ir šis topiks.

 

SQL zemāk spoiler tagos:

 

 

CREATE TABLE IF NOT EXISTS `testa_tabula` (
`id` int(5) unsigned NOT NULL AUTO_INCREMENT,
`person_id` int(5) unsigned NOT NULL DEFAULT '0',
`stats_id` int(5) unsigned NOT NULL DEFAULT '0',
`value` int(5) unsigned NOT NULL DEFAULT '0',
`filter1` tinyint(1) unsigned NOT NULL DEFAULT '0',
`filter2` tinyint(1) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `person_id` (`person_id`,`stats_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;

INSERT INTO `testa_tabula` (`id`, `person_id`, `stats_id`, `value`, `filter1`, `filter2`) VALUES
(1, 1, 1, 10, 0, 0),
(2, 1, 1, 10, 0, 1),
(3, 1, 2, 100, 3, 0),
(4, 1, 2, 200, 0, 2),
(5, 2, 2, 500, 0, 0),
(6, 2, 1, 100, 0, 0),
(7, 3, 2, 10, 2, 3),
(8, 3, 3, 10, 0, 0),
(9, 4, 2, 5, 0, 0),
(10, 4, 3, 7, 0, 1),
(11, 5, 2, 1, 1, 0),
(12, 5, 3, 11, 0, 0),
(13, 5, 4, 6, 0, 1);

 

 

Selects no testa tabulas.

 

SELECT
    person_id,
    coalesce(sum(case when stats_id = 1 then skaits end), 0) as s1,
    coalesce(sum(case when stats_id = 2 then skaits end), 0) as s2,
    coalesce(sum(case when stats_id = 3 then skaits end), 0) as s3,
    coalesce(sum(case when stats_id = 4 then skaits end), 0) as s4
FROM
    (
        SELECT 
            person_id, 
            stats_id, 
            COUNT(*) as skaits
        FROM testa_tabula
        GROUP BY person_id, stats_id
        ORDER BY skaits DESC
    )
AS X
GROUP BY person_id

Rezultāts vizuāli:

 

Meklēts tiek stats_id skaits sagrupēts pēc personas id.

Ar iespēju pēc tam sortēt pēc katras s1, s2, s3, s4... kolonnas, vienlaicīgi redzot pārējos datus.

Stats_id ar laiku palielināsies, bet ne bezgalīgi.

 

Kverija explain:

 

Uz puslīdz reāliem datiem, 90% kverija aizņem copying to tmp table, jo jāsasummē ir diezgan daudz, salīdzinot ar iegūstamo rezultātu.

 

Ieteikumi? Šo te kveriju ir iespējams uzlabot? Vai taisīt kaut kādu starptabulu ar jau sasummētiem datiem?

Joks tāds, ka esošo rezultātu kādreiz būs nepieciešams vēl arī filtrēt pēc citām kolonnām - filter1, filter2. Tāpēc vienīgais variants ir sasummēt grupējot pēc person_id, stats_id, filter1, filter2, kā rezultātā ierakstu skaits nemaz tik ļoti nesamazināsies.

 

 

//update:

Ar starptabulu sanāk daudz maz normāli, ja ignorē filter1 un filter2.

explain rezultāts: type=index, rows=81, extra=using where

Edited March 19, 2014 by Val

[Roze]

Ar iespēju pēc tam sortēt pēc katras s1, s2, s3, s4... kolonnas, vienlaicīgi redzot pārējos datus.

 

 

Ja datu ir daudz un kverijam jāizpildās kaut cik pārskatāmā laikā (pieņemsim standarta 1-2 sec max lapas ielādei), tad faktiski ir jāatmet doma par datu kārtošanu pēc neeindeksētiem un vēl jo vairāk temporāriem laukiem.

 

Starptabula ar sarēķinātiem varētu būt jēdzīgākais variants.

[e-remit]

Pirmais, kas duras acīs, priekš kam tev subquerie tiek orderēts?

ORDER BY skaits DESC

Izņem to un jau jāuzlabojas izplildes plānam.

[Val]

Izņem to un jau jāuzlabojas izplildes plānam.

Jap, mana kļūda.

Tiku vaļā no viena temporary un filesort iekš testa kverija.

 

 

Edited March 19, 2014 by Val

[Val]

Starptabula ar sarēķinātiem varētu būt jēdzīgākais variants.

Es vēl cerēju, ka mysql ir kas labs izdomāts šādiem gadījumiem. Jāpārstāj sapņot.

Daļēji jau patestēju vienkāršotā variantā.

Pa dienu jāuztaisa pilnais variants. Jautrība ar datu integritāti.

[draugz]

Es lietotu kau ko šādu

SELECT
    person_id,
    concat( "{",group_concat( concat( stats_id, ":", skaits ) ),"}") as json_stats
FROM
    (
        SELECT 
            person_id, 
            stats_id, 
            COUNT(*) as skaits
        FROM testa_tabula
        GROUP BY person_id, stats_id
    )
AS X
GROUP BY person_id

Pirmo concat kas pievieno { un } var arī likt PHP pusē :)

 

Vienīgi, group_concat laikam bija limitēts uz maksimālo atmiņas daudzumu

[Roze]

Es vēl cerēju, ka mysql ir kas labs izdomāts šādiem gadījumiem. Jāpārstāj sapņot.

Nu mysqls jau faktiski arī mēģina kaut ko darīt lietas labad (un ja ierakstu nav daudz, tad viss notiek (samērā) ātri) - proti veido to starptabulu, bet:

1. Tās starptabulas veidojas uz katru pieprasījumu

2. Atkarībā no datu apjomiem temporārās tabulas var sākt rakstīties no atmiņas uz disku ( http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_tmp_table_size ) - tad paliek pavisam slikti.

 

Pēc pieredzes tabulām, kur ierakstu skaits sākt pārsniegt pārsimt tūkstošus pilnībā mēģinu izvairīties no GROUP BY, COUNT(*), ORDER pēc lauka (ja pieprasījumu neapmierina kāds indeks pilnībā).

[Val]

Sākuma dati:

27 ms uz lokālās kastes, 5.3 ms uz servera ar SQL_NO_CACHE

 

Starpsummas, grupējot pēc filtriem, kverijā mazāk jāsummē:

17 ms un 3.5 ms

 

Starpsummas, ar izmestiem filtriem, kverijā viss jau sasummēts:

11 ms un 2.5 ms

 

 

Var vienīgi vēl iekešot apmeklētājam redzamos datus uz noteiktu minūšu skaitu.

Īstenībā pat varētu atstāt pirmo versiju un pagaidām aizmirst par starpsummām, lai dati nedublētos. Pie tām atgriezties, ja sāks nejēgā slogot kasti.

 

 

Kā arī pārtaisīt šo tabulu uz

id, personas_id, filter1, filter2, stats1, stats2, stats3, stats4, ..., stats22, ... galigi negribas.

Edited March 21, 2014 by Val

[draugz]

Tu manu variantu pamēģināji? uz taviem testa datiem mans sql izgāja bez "Using temporary"

[Val]

Jā, pamēģināju, Mysql 5.0.92

 

Uz localhosta ar 5.5.36, ir lasāmāk.

 

Kopējais rezultāts diezgan līdzīgs. Kā arī šajā kverijā pat nav ietverta galarezultāta sortēšana pēc json datiem.

Edited March 21, 2014 by Val