Keskustelu: Nettisivujen teko: MySQL viestejä tietyn ajan sisällä

Minulla on seuraavanlainen kysely tietokannasta.

SELECT a.user_id, COUNT(a.message_id) AS messages
FROM forum_messages AS a
LEFT JOIN forum_messages AS b ON a.user_id = b.user_id
WHERE a.category_id = ? AND a.time >= ? AND a.time <= ?
GROUP BY a.user_id
HAVING messages > 20
ORDER BY messages, COUNT(b.message_id)/messages DESC LIMIT 5

En todellakaan ole vielä niin sinut SQL:n kanssa, että huomaisin missä mennään pieleen. Koko ajan tuntuu tulevan jotain erroria ja tuntuu, että virheet pyörivät ympyrää. Toisen ratkaistuani olen takaisin edellisessä ongelmassa.

Tarkoitus olisi siis hakea tiettyyn kategoriaan lähetetyistä viesteistä lähettäjien id:t ja viestimäärät valitulta aikaväliltä, mikäli henkilö on lähettänyt vähintään 20 viestiä kyseiseen kategoriaan. Saadut tulokset olisi tarkoitus järjestää ensisijaisesti viestimäärän mukaan, mutta jos jotkut ovat lähettäneet yhtä monta viestiä niin lasketaan henkilöiden kaikki viestit (kaikista kategorioista) tältä valitulta aikaväliltä ja järjestetään sen mukaan montako prosenttia tähän valittuun kategoriaan lähetetyt viestit ovat henkilön kaikista viesteistä.

Jos siis kaksi henkilöä ovat molemmat lähettäneet 20 viestiä haluttuun kategoriaan valitun aikavälin aikana, niin katsotaan montako viestiä he ovat kaikenkaikkiaan lähettäneet tänä samaisena aikana. 1. henkilö on lähettänyt 200 ja 2. henkilö 100 viestiä. Näin 2. henkilö tulee järjestyksessä ensin, koska 20/200 = 0.1 ja 20/100 = 0.2 ja 0.1 < 0.2

Voisiko joku kertoa missä kaikkialla menee pieleen ja mieluusti otan vastaan vaikka ihan valmiin kyselyn. :D Kiitos.

Ihan sivukysymyksenä kysyisin, että mitä eroa oikeastaan on timestamp ja datetime arvoilla? Tuo forum_messages taulun time kenttä ei yleensä saa tämän hetkistä kellonaikaa vaan siihen saatetaan kirjata melkeinpä mikä tahansa päivämäärä (kuitenkin väliltä 2008 - nyt). Kannattaisiko minun siis muuttaa tuo time kenttä datetime arvosta timestamp arvoksi?

No näin ainakin Putkasta tuli (toki eri nimillä):

SELECT * FROM (
  SELECT user_id, SUM(category_id = ?) AS n_category, COUNT(*) AS n_total
  FROM forum_messages
  WHERE time BETWEEN ? AND ?
  GROUP BY user_id
) AS tmp
WHERE n_category >= 20
ORDER BY n_category DESC, n_category / n_total DESC
LIMIT 5

Tuo toimii loistavasti! :)
Käytän eräällä sivulla tuota kyselyä 30 kertaa ja tällöin sivunlataus näyttää kestävän turhankin kauan, jos tälle valitulle aikavälille sattuu kaikki 700 000 riviä, jotka taulussani on. Vähemmillä riveillä ( < 300 000 ) sivu lataa kyllä riittävän nopeasti.
Olisiko antaa neuvoja haun optimoimiseksi?

Entä miten saan samanlaisen tuloksen ulos sellaisesta taulusta, joka sisältää kentät user_id, category_id ja messages (eli käyttäjän kategoriaan kirjoittamien viestin määrä).

Nyt pitäisi siis tuo n_total arvo saada laskettua tyyliin "SUM(messages) AS n_total GROUP BY user_id", mutta kun samalla pitäisi saada ulos myös se käyttäjän tiettyyn kategoriaan kirjoittama viestimäärä tyyliin "messages WHERE category_id = ? GROUP BY user_id" ja tällöin en omin neuvoin osaa enää saada tuota n_total arvoa ulos.