[MySQL] Limit op een subquery / join

[Sponge]

Hi,

Ik zit op dit moment met een query waar ik echt even niet uitkom. Via de search had ik al het een en ander gevonden, maar toch krijg ik het niet werkend.

Het probleem

In mijn statistieken systeem archief/verwerk ik rauwe gegevens naar iets wat het een en ander kan zeggen. Eerst deed ik dit gehele verwerken in PHP, maar op dit moment heb ik al heel veel geport naar C++, wat qua snelheid al het een en ander verbeterd heeft.

Mijn streven is vooral om zo veel mogelijk alles direct in queries te doen, zonder dat er dus nog een applicatie laag tussen zit die dingen afhandeld. Nu heb ik echter een query nodig waar ik dus niet uitkom, wat er moet gebeuren is aardig eenvoudig. Even versimpelt weergegeven:

* Er is een tabel 'stats' met veel gegevens, waaronder de entrypageid. Dit is de id van de pagina waar de bezoeker op binnenkwam.

* Er is date field in deze tabel, wat aangeeft wanneer de bezoeker langskwam.

* Er is een tabel 'archive' waarin alle dates (per dag dus een!) sinds de start van de telling tot nu als date field opgeslagen zijn.

Nu wil ik per dag bijvoorbeeld de top 30 van de entrypageid's hebben. Dus per dag een lijst van 30 id's van hoog naar laag, op pagina 1 kwamen bijv 10 bezoekers, op pagina 2 maar 8, op pagina 3 slechts 7.. etc.

Echter krijg ik dit dus niet voor elkaar, en zit hier al enkele uren mee te klooien. Per dag een query is niet de bedoeling: Dit zou nu al 1000 queries zijn. Nu worden de statistieken natuurlijk niet elke keer vanaf het start punt verwerkt... maar toch zou ik dit graag goed hebben. Het zou dan toch ook gewoon mogelijk moeten zijn?

Per dag zou het zo zijn:

code:

INSERT INTO  archive_entries (moment, pageid, visitors)
SELECT s.date, s.entrypageid , COUNT( s.entrypageid  FROM statistics s WHERE s.date= "2007-01-20"  GROUP BY entrypageid ORDER BY num DESC LIMIT 30;

# (0.156sec)

Ik heb al zitten experimenteren met temporary tables, sub queries en full outer joins, maar ik loop steeds tegen iets aan waardoor het gewoon niet goed gaat, of alleen de eerste dag, etc. Ik heb helaas al die queries niet meer omdat ik een query had geschreven die dependable was, en nogal hard gestopt moest worden .

Dus misschien dat iemand hier die een goede suggestie heeft?

[Voutloos]

Tja de schoonheidsprijs zal je er niet mee winnen, maar je zou een hele rits van die dagqueries met UNION ALL kunnen doen.

[Sponge]

Voutloos, helemaal niet een onaardig idee an sich .

Zojuist heb ik ~1500 regels (dus ongeveer 750 queries) via een UNION ALL samengevoegd, en geinsert. Dit kostte slechts 1,3 seconden volgens Navicat. Ik twijfelde daar wat aan, en heb het even in m'n applicatie ingebouwd, en die zegt ook 1,343 seconden. Dat is met SQL_NO_CACHE in elke query.

Dat is al een heel stuk beter dan de losse queries!

[Voutloos]

Ok dan.

Overigens heeft iets als SQL_NO_CACHE totaal geen nut, die vlag doet enkel iets bij pure SELECT queries.

[Sponge]

Nou, dan zie ik morgen wel of het nog steeds 1.3 sec is als ik de mysqld opnieuw opstart .

Maar toch vraag ik mij nog steeds af of er geen andere oplossing is via een subquery/join.

[Voutloos]

Sponge schreef op zondag 01 maart 2009 @ 22:34:
Nou, dan zie ik morgen wel of het nog steeds 1.3 sec is als ik de mysqld opnieuw opstart .

Of je leest hoe de query cache werkt. Executive summary: query cache slaat onthoudt hash(totale select query) + resultset paren. Er is geen query cache logica mbt subqueries.

Maar toch vraag ik mij nog steeds af of er geen andere oplossing is via een subquery/join.

Tja, als je in sets denkt, kom je dus altijd op een union oplossing uit.