Keskustelu: Ohjelmointikysymykset: SQL-haku monesta taulusta ja järjestys

Morjens taas

MySQL-kyselyä koitan kasata, joka hakisi tietoa yhteensä neljästä taulusta. Saan tiedot kyllä tauluista ulos, ja ihan oikeatkin tiedot - suurimmaksi osaksaan, osa vain jää puuttumaan.

SELECT
alueet.id AS alueID, alueet.kategoria, alueet.nimi, alueet.jarjestys,
viestit.id AS viestitID, viestit.lahettaja_id, viestit.aika, viestit.aihe_id, viestit.alue,
aiheet.otsikko, aiheet.id AS aiheID,
kayttajat.id AS kayttajaID, kayttajat.tunnus
FROM alueet, viestit, aiheet, kayttajat
WHERE (
  alueet.kategoria = 1
  AND viestit.alue = alueet.id
  AND kayttajat.id = viestit.lahettaja_id
  AND viestit.aihe_id = aiheet.id
)
ORDER BY viestit.aika DESC

Tämmöinen viritys tuli tehtyä. Tarkoitus olisi hakea alueet-taulusta kaikki alueet, joissa alueet.kategoria = 1. Sitten pitäisi hakea uusin viesti tällä alueella käyttäen hyväksi alueet.id:tä. Koska tarkoitus on näyttää uusimman viestin tiedot tällä alueella, haetaan myös lähettäjän tiedot (taulu kayttajat) ja aiheen tiedot (taulu aiheet). En sitten tiedä onko nopeampaa ja helpompaa tietokannalle ja käyttäjälle, jos tehdään suuri kysely joka hakee monesta taulusta tietoa, vai se että pidetään kaikki tieto samassa taulussa moneen kertaan. Olettaisin, että näin se olisi parempi. Koskaan ei tule kyllä tälle käyttäjiä minua enempää, että ei se siihen kaadu. Tarkoitus on harjoitella ns. parempaa MySQL:ää ja PHP:tä, ja tehdä ohjelmasta mahdollisimman optimoitu.

Mikäs tuota kyselyä vaivaa, kun se palauttaa oikeat tiedot, mutta vain yhdeltä alueelta jossa alueet.kategoria = 1? Ja niitäkin sieltä tulee enemmän kuin pitäisi, kaikki viestit jotka on lähetetty alueelle x. Kyselyn tulos on tälläinen.

Ethän missään kohti edes rajoita tulosten määrää, etkä järkevästi voikaan tuossa kyselyssä.

Jos id kasvaa aikajärjestyksessä, uusimman viestin id on MAX(viestit.id) ja alueittain hakuun tarvitaan GROUP BY alueet.id. Jos haluat välttämättä hakea loputkin tiedot samassa kyselyssä, voit kokeilla alikyselyitä:

SELECT kaikenlaista ... WHERE viestit.id IN (
  SELECT MAX(viestit.id) ... GROUP BY alueet.id
)

Itse käyttäisin uusimpien viestien jäljittämiseen erillistä taulua ja triggereitä. Suurempien viestimäärien kanssa tuollaiset epätoivoiset sorttaukset eivät yksinkertaisesti toimi.

delimiter |

CREATE TRIGGER update_newest AFTER INSERT ON messages
FOR EACH ROW BEGIN
  UPDATE newest SET message_id = NEW.id, time = NEW.time WHERE area_id=NEW.area_id;
END;
|

delimiter ;

-- =>

SELECT * FROM newest WHERE category = 'x' ORDER BY time;

Koska triggerissä on UPDATE-lauseke, pitää kyseiset rivit tietysti luoda ensin esimerkiksi alueiden lisäämisen yhteydessä.

The Alchemistin ehdotus olikin aika hyvä. Noudatin sitä (Jätin kyllä lopusta ylimääräisen pystyviivan pois), ja tein uuden taulun uusimmille viesteille. Jostain syystä en saa lisättyä triggeriä tauluun. PhpMyAdmin sanoo, että kysely olisi suoritettu virheettömästi, mutta show triggers ei palauta yhtään triggeriä eikä mitään tapahdu kun viesti lisätään.

delimiter |

CREATE TRIGGER paivita_uusimmat_viestit AFTER INSERT ON viestit
FOR EACH ROW BEGIN
  UPDATE uusimmat SET viesti_id = NEW.id, aika = NEW.aika WHERE alue = NEW.alue_id;
END;

delimiter ;

Macro kirjoitti:

The Alchemistin ehdotus olikin aika hyvä. Noudatin sitä (Jätin kyllä lopusta ylimääräisen pystyviivan pois)...

Veikkaisin että tuo "ylimääräinen" pystyviiva on tarpeellinen, sillä eihän tuo taida tajuta missä kysely päättyy ilman tuota erotimerkkiä. Joku varmaan kumoaa tämän mikäli puhun ihan omiani ;).

Mizou kirjoitti:

Macro kirjoitti:

The Alchemistin ehdotus olikin aika hyvä. Noudatin sitä (Jätin kyllä lopusta ylimääräisen pystyviivan pois)...

Veikkaisin että tuo "ylimääräinen" pystyviiva on tarpeellinen, sillä eihän tuo taida tajuta missä kysely päättyy ilman tuota erotimerkkiä. Joku varmaan kumoaa tämän mikäli puhun ihan omiani ;).

Tuskin kumoaa, nimittäin jostain syystä tälläkertaa PhpMyAdmin ei antanut virhettä kun lisäsin viivan sinne. Eilen se antoi virhettä tuosta pystyviivasta. Taisi olla jotain muuta pielessä. Ihmettelempä vain, että miksi se kumminkin pääsi läpi.