[sql] erg langzame query, hoe kan ie sneller

[BasieP]

beste mensen.

ik heb een sql probleempje.
ik heb een tabel 'stats' met daarin een aantal rijen, belangrijkste daarvan zijn 'ip' en 'date'

ik wil van deze tabel PER dag (date) het aantal users (ip) krijgen.

ik heb daarvoor deze query

SQL:

SELECT date, count(*) AS hits
FROM stats
WHERE id IN (
  SELECT id
  FROM stats
  GROUP BY ip, date
)
GROUP BY date
ORDER BY date

id is een automatisch gegenereerd veld, dat ik hier gebruik om unique waardes gebruiken vanuit me subquery.
ik vind vooral dat laatste erg omslachtig en denk eerlijk gezegt dat dat ook anders kan. ik heb alleen geen idee hoe...

het probleem is dus dat deze query soms wel 20 seconden duurt, en dat lijkt me toch echt veel te lang

[ Voor 11% gewijzigd door BasieP op 19-07-2005 22:55 ]

[momania]

SQL:

SELECT date, ip, count(id) AS hits FROM stats GROUP BY ip, date

werkt niet?

Die count(*) is iig vaak erg 'duur' en een "WHERE x IN" is ook een dure

[ Voor 20% gewijzigd door momania op 19-07-2005 22:59 ]

[BasieP]

momania schreef op dinsdag 19 juli 2005 @ 22:58:

SQL:

SELECT date, ip, count(id) AS hits FROM stats GROUP BY ip, date

werkt niet?

Die count(*) is iig vaak erg 'duur' en een "WHERE x IN" is ook een dure

hij doet het wel, maar het resultaat is signifikant anders..

ik wil nu per dag 1 row terug krijgen. met daarin de count van de ip's

wat jouw query terug geeft is een hele berg rijen, nl alle rijen waar het ipadres OF de datum anders is..
dus per dag evenveel rijen als er ip's zijn voor die dag.


wat ik mij nu afvraag is, of het niet sneller is om dit dan gewoon in php op te lossen..
ik kom er namelijk net achter dat hij ook helemaal niks laat zien voor een dag waar geen hits zijn, en ik daar natuurlijk een 0 terug wil hebben

[ Voor 33% gewijzigd door BasieP op 19-07-2005 23:06 ]

[momania]

Ok, dan moet je dus het volgende doen:

SQL:

SELECT date, count(ip) AS hits FROM stats GROUP BY date

Dan heb je alleen maar een groepering op datum en tel je dus alle records van die datum via count(ip)

Wat ik je eerder gaf in idd een groepering per datum en ip. Dan kan je dus per datum zien hoe vaak een bepaald ip in je database staat

BasieP schreef op dinsdag 19 juli 2005 @ 23:03:
[...]
wat ik mij nu afvraag is, of het niet sneller is om dit dan gewoon in php op te lossen..
ik kom er namelijk net achter dat hij ook helemaal niks laat zien voor een dag waar geen hits zijn, en ik daar natuurlijk een 0 terug wil hebben

Dan zou je idd een where moeten opgeven met een datum range oid.


Zoiets zou misschien moeten werken:

[code=sql]
SELECT
date,
CASE WHEN date IN (SELECT date FROM stats WHERE date between x AND y) THEN
count(ip)
ELSE
0
END AS hits
FROM stats
WHERE date between x AND y
GROUP BY date[/]
Zal alleen weer iets trager zijn vanwege de "WHERE x IN", maar als je een goede index op die date kolom hebt moet dat wel beter gaan.

Bedenk me net dat dit dus helemaal niet gaat werken

offtopic:
Zoals je al ziet in de syntax highlights is date een keyword, dus ik zou wel voor die kolom een andere naam kiezen

[ Voor 66% gewijzigd door momania op 19-07-2005 23:19 ]

[BasieP]

momania schreef op dinsdag 19 juli 2005 @ 23:06:
Ok, dan moet je dus het volgende doen:

SQL:

SELECT date, count(ip) AS hits FROM stats GROUP BY date

Dan heb je alleen maar een groepering op datum en tel je dus alle records van die datum via count(ip)

maar dit ook niet, omdat je nu alleen order by date doet. ik krijg dus wel 2 rijen terug (wat ik wil) maar de count die hij doet, geld voor ALLE ip's niet voor alle ip's slechts 1x.
dus hij geeft nu niet het aantal unique hits van die dag, maar het totale aantal hits van die dag.

offtopic:
Zoals je al ziet in de syntax highlights is date een keyword, dus ik zou wel voor die kolom een andere naam kiezen

offtopic:
ja de tabel, en namen heten anders, maar heb ze even ingekort voor op 't forum. helaas even niet aan date gedacht

[momania]

BasieP schreef op dinsdag 19 juli 2005 @ 23:26:
[...]
dus hij geeft nu niet het aantal unique hits van die dag, maar het totale aantal hits van die dag.

Aaah, je wilt unique!

SQL:

count(distinct ip)

should do the trick