[MYSQL] Zelfde tabel meerdere keren gebruiken in select

[CU2morO]

Bij het zoeken naar een goede manier om rankings uit te rekenen ben ik op deze website terecht gekomen

http://www.1keydata.com/sql/sql-rank.html

Wat ze hier doen is simpel, Selecteer de dataset 2x, en gebruik de 2e result set om alle scores hoger en gelijk dan een record te tellen. Dit is de rank.

In mijn situatie echter, zorgt dit voor een probleem. Mijn scores worden dynamisch berekend en komen niet direct uit de database. Als ik voor zowel dataset 1 als dataset 2 (in het voorbeeldje a1 en a2 genoemd) deze tamelijk complexe query uit moet voeren, duurt deze plots 3+ seconden ipv ~350ms.

Ik denk er vast compleet naast, maar kan ik niet iets doen als:

code:

SELECT * 
FROM
(SELECT [complexe query]) AS a1,
(SELECT a1) AS a2
WHERE a1.score<= a2.score OR (a1.score = a2.score AND a1.team_id = a2.team_id)
GROUP BY a1.team_id, a1.score
ORDER BY a1.score DESC, a1.team_id DESC;

Dit geeft echter de error dat a1 niet bekend is in mijn database. Ik moet per sé die complexe query 2x uitvoeren om dit te bereiken.

Is er een handigere manier om dit te bereiken?

[cariolive23]

En hoe ziet jouw complexe query eruit? En wat zegt EXPLAIN over deze query? Wanneer het lijkt dat deze query het probleem is, ga je daar dan eerst op focussen. Wanneer dat klaar is, ga je met EXPLAIN achterhalen hoe bovenstaande query wordt uitgevoerd en waar de knelpunten zitten.

Zonder de resultaten van EXPLAIN heb je geen idee wat er aan de hand is, ga die dus eerst opvragen.

[D-Raven]

Je zou een temp table kunnen gebruiken

Dan krijg je zoiets als dit

code:

Select [complexe select] into #Temp from [rest van complexe query]
select * from #temp where ...etc

wel aan het einde je temp table weer droppen.

Meer info: hier

[ Voor 25% gewijzigd door D-Raven op 25-08-2009 15:15 . Reden: Tis mysql, niet mssql :+ ]

[CU2morO]

De complexe query ziet er als volgt uit

code:

SET @dLow = DATE_SUB(dBase, INTERVAL 104 WEEK);
SET @dPrev = DATE_SUB(dBase, INTERVAL 1 WEEK);

SELECT t.id AS team_id, t.name AS team, cat.id AS cat_id, cat.name AS category, SUM(degraded_score(p.place, c.bonus, c.date, dBase)) AS score, SUM(degraded_score(p.place, c.bonus, c.date, @dPrev)) AS prev_score, SUM(IF(p.place<=10,1,0)) AS Top10, SUM(IF(p.place<=5,1,0)) AS Top5, SUM(IF(p.place=1,1,0)) AS Wins
FROM participations p, teams t, contests c, contest_categories cc, categories cat
WHERE p.concat_id = cc.id AND p.team_id = t.id AND cc.contest_id = c.id AND cc.category_id = cat.id AND ((iCat = 0) OR (cat.id = iCat)) AND c.date BETWEEN @dLow AND dBase
GROUP BY cc.category_id, p.team_id

Kort samengevat: Een participation heeft een plaats (1st, 2nd, etc). Dit is X punten waard, op basis van hoe lang het geleden is. Mijn functie Degraded_Scores berekend dit. Verder wordt het aantal top10, top5 en wins getoond.
iCat is een categorie-ID die meegegeven wordt.
dBase is de datum waarop de score gebaseerd is. Voor testen pak ik CURDATE()
@dPrev is de dBase - 1week, om straks de trend te berekenen (4 plaatsen gestegen, etc).

De 2 SET's heb ik uiteraard al buiten de hele select e.d. staan. Maar voor de duidelijkheid hier even bij gepaste.


EXPLAIN geeft volgende op deze 'complexe' query

code:

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   SIMPLE  cat const   PRIMARY PRIMARY 4   const   1   Using temporary; Using filesort
1   SIMPLE  p   ALL \N  \N  \N  \N  32157   
1   SIMPLE  cc  eq_ref  PRIMARY PRIMARY 4   testdb.p.concat_id  1   Using where
1   SIMPLE  t   eq_ref  PRIMARY PRIMARY 4   testdb.p.team_id    1   
1   SIMPLE  c   eq_ref  PRIMARY PRIMARY 4   testdb.cc.contest_id    1   Using where

[ Voor 18% gewijzigd door CU2morO op 25-08-2009 15:24 ]

[CU2morO]

D-Raven schreef op dinsdag 25 augustus 2009 @ 15:10:
Je zou een temp table kunnen gebruiken

Dan krijg je zoiets als dit

code:

Select [complexe select] into #Temp from [rest van complexe query]
select * from #temp where ...etc

wel aan het einde je temp table weer droppen.

Meer info: hier

code:

CREATE TEMPORARY TABLE temp AS (SELECT t.id AS team_id, t.name AS team, cat.id AS cat_id, cat.name AS category, SUM(degraded_score(p.place, c.bonus, c.date, dBase)) AS score
FROM participations p, teams t, contests c, contest_categories cc, categories cat
WHERE p.concat_id = cc.id AND p.team_id = t.id AND cc.contest_id = c.id AND cc.category_id = cat.id AND ((iCat = 0) OR (cat.id = iCat)) AND c.date BETWEEN @dPrevLo AND dBase
GROUP BY cc.category_id, p.team_id);

SELECT COUNT(a2.score) AS rank, a1.*
FROM
temp AS a1,
temp AS a2
WHERE a1.score <= a2.score or (a1.score=a2.score and a1.team_id = a2.team_id)
GROUP BY a1.team_id, a1.score
ORDER BY a1.score DESC, a1.team_id DESC;

DROP TEMPORARY TABLE temp;

Na een paar ms geeft dit de fout 'Error Code : 1137, Can't reopen table: 'a1''

http://forums.mysql.com/read.php?20,198873,198873
Dit lijkt er op te wijzen dat MYSQL dit gewoonweg niet kan

[ Voor 4% gewijzigd door CU2morO op 25-08-2009 16:51 ]

[plagvreugd]

Alternatief: gebruik van rank() over en partition by en dat soort shizzle is geen optie in MySQL?

[Kalentum]

Wat je ook nog zou kunnen proberen is die complexe query in een view stoppen en dan die view gebruiken als dataset.

[Remus]

Helaas kent MySQL geen common table expressions, anders was dat het ideale middel geweest voor dit probleem. Zie http://bugs.mysql.com/bug.php?id=16244

[cariolive23]

plagvreugd schreef op dinsdag 25 augustus 2009 @ 20:45:
Alternatief: gebruik van rank() over en partition by en dat soort shizzle is geen optie in MySQL?

Niet in MySQL, wel in PostgreSQL sinds versie 8.4. Misschien is migreren een optie, dat hoeft geen grote klus te zijn.

[_Sunnyboy_]

Als de rank dynamisch bepalen zo duur is (qua tijd) kan je er ook nog voor kiezen deze rank 1 keer uit te zoeken en op te slaan, en dit bij elke update opnieuw te doen. Bij een view kan je deze rank info dan gebruiken.

Is natuurlijk alleen handig als er veel meer views zijn dan updates, en als jij controle hebt over alle updates. Ik geef toe dat het niet mooi is van uit een normalisatie point of view, maar soms is dit gewoon de beste manier.

[ Voor 7% gewijzigd door _Sunnyboy_ op 25-08-2009 23:26 ]

[CU2morO]

rutgerw schreef op dinsdag 25 augustus 2009 @ 21:08:
Wat je ook nog zou kunnen proberen is die complexe query in een view stoppen en dan die view gebruiken als dataset.

Hoe zou ik dat dan moeten doen in dit geval? Kun je in een view ook werken met variabelen zoals de uitgangs datum in mijn query die hierboven gepaste is?

Of zou ik in stored procedure de view dynamisch moeten aanmaken en weer weggooien?

[CU2morO]

OK Ik heb na het nodig gepruts het volgende bereikt. Ik krijg nu de huidige score, de huidige rank, de score van vorige week en de rank van vorige week.

De query die ik hiervoor gebruik is als volgt:

code:

SET @dLow = DATE_SUB(dBase, INTERVAL 104 WEEK);
SET @dPrevHi = DATE_SUB(dBase, Interval 1 WEEK);
SET @dPrevLo = DATE_SUB(@dLow, INTERVAL 1 WEEK);

SELECT b1.*, b2.prev_score, b2.prev_rank
FROM
 (SELECT a1.*, COUNT(a2.score) AS rank
  FROM
   (SELECT p.team_id, t.name AS team, cc.category_id, SUM(IF(p.place<=10,1,0)) AS Top10, SUM(IF(p.place<=5,1,0)) AS Top5, SUM(IF(p.place=1,1,0)) AS Wins, SUM(degraded_score(p.place, c.bonus, c.date, dBase)) AS score
    FROM participations p 
    INNER JOIN teams t ON t.id = p.team_id 
    INNER JOIN contest_categories cc ON cc.id = p.concat_id 
    INNER JOIN contests c ON c.id = cc.contest_id
    WHERE ((iCat = 0) OR (cc.category_id = iCat)) AND c.date BETWEEN @dLow AND dBase
    GROUP BY cc.category_id, p.team_id) as a1,
   (SELECT p.team_id, cc.category_id, SUM(degraded_score(p.place, c.bonus, c.date, dBase)) AS score
    FROM participations p 
    INNER JOIN teams t ON t.id = p.team_id 
    INNER JOIN contest_categories cc ON cc.id = p.concat_id 
    INNER JOIN contests c ON c.id = cc.contest_id
    WHERE ((iCat = 0) OR (cc.category_id = iCat)) AND c.date BETWEEN @dLow AND dBase
    GROUP BY cc.category_id, p.team_id) AS a2
  WHERE a1.score > 0 AND (a1.score <= a2.score OR (a1.score = a2.score AND a1.team_id = a2.team_id))
  GROUP BY a1.team_id, a1.score) AS b1,

 (SELECT a1.*, COUNT(a2.prev_score) AS prev_rank
  FROM
   (SELECT p.team_id, cc.category_id, SUM(degraded_score(p.place, c.bonus, c.date, @dPrevHi)) AS prev_score
    FROM participations p 
    INNER JOIN teams t ON t.id = p.team_id 
    INNER JOIN contest_categories cc ON cc.id = p.concat_id 
    INNER JOIN contests c ON c.id = cc.contest_id
    WHERE ((iCat = 0) OR (cc.category_id = iCat)) AND c.date BETWEEN @dPrevLo AND dBase
    GROUP BY cc.category_id, p.team_id) as a1,
   (SELECT p.team_id, cc.category_id, SUM(degraded_score(p.place, c.bonus, c.date, @dPrevHi)) AS prev_score
    FROM participations p 
    INNER JOIN teams t ON t.id = p.team_id 
    INNER JOIN contest_categories cc ON cc.id = p.concat_id 
    INNER JOIN contests c ON c.id = cc.contest_id
    WHERE ((iCat = 0) OR (cc.category_id = iCat)) AND c.date BETWEEN @dPrevLo AND dBase
    GROUP BY cc.category_id, p.team_id) AS a2
  WHERE a1.prev_score <= a2.prev_score OR (a1.prev_score = a2.prev_score AND a1.team_id = a2.team_id)
  GROUP BY a1.team_id, a1.prev_score) AS b2

WHERE b1.team_id = b2.team_id AND b1.category_id = b2.category_id
ORDER BY b1.category_id, b1.score DESC;

Executietijd hiervan ligt rond de 4-5 hele seconden als ik het voor één categorie doe.

Het is blijkbaar niet mogelijk om binnen MySQL de binnenste query op te slaan en her te gebruiken. Hoewel 4-5 seconden best acceptabel is voor iets dat niet zo heel vaak veranderd, zit ik er aan te denken om een vorm van database-caching toe te passen.

Aangezien een view niet werkt met variabelen, zit ik er aan te denken om het geheel in een temporary table te gooien.

Deze temp table blijft dan ongeveer een week in de lucht, waarna hij wordt gedropt en gevuld wordt door een nieuwe call naar bovenstaande stored proc.

Is dit verstandig? Zijn temp tables gemaakt om een week lang te leven? Of zijn ze meer bedoeld om te maken en droppen tijdens een stored proc call?

[cariolive23]

Is dit verstandig?

Nee, zodra de connectie wegvalt/wordt verbroken, wordt de temp table gedropt. Je kunt dus met geen mogelijkheid garanderen dat jouw temp table bestaat. Je kunt uiteraard wel een gewone tabel maken en daar de data inzetten. Dat je deze dan 1x per week van nieuwe data voorziet, so be it.

Deze aanpak lijkt dan in de verte wat op een materialized view.

[CU2morO]

OK. Ik had er inderdaad niet bij stilgestaan dat een temp table weg is bij het herstarten van de DB.

Meterialized view... Wat ik dan niet snap... Waarom mag ik dit niet gewoon in een view gooien? Dit is toch waar een View voor bedoeld is? Berekende kolomwaardes, maar geen variabelen zijn toegestaan?

Een 'echte' table aanmaken is dan de beste oplossing zoals je zelf ook al zegt. En dan een stored procedure die hem truncate en hervult 1x per week nadat alle wedstrijdgegevens zijn ingevuld.

[winkbrace]

Mijn ervaring is dat een OR de query soms trager maakt. Je zou kunnen proberen de
WHERE x <= y
OR a = b AND c = d
te kunnen herschrijven naar

SQL:

WHERE CASE WHEN x <= y then 1 
           WHEN a = b AND c = d then 1
           ELSE 0
      END = 1

Soms helpt dit Hij zal in ieder geval de OR niet uitvoeren als aan de eerste voorwaarde voldaan is.

[cariolive23]

Een VIEW moet ook de onderliggende query uitvoeren. Wanneer deze query langzaam is, zal de VIEW dus langzaam zijn. Vaak wel wat sneller dan normaal de query uitvoeren, de query hoeft niet nogmaals worden geparst door de database, maar moet nog steeds alle records uit de diverse tabellen bijelkaar harken. En dat kost tijd.