Keskustelu: Nettisivujen teko: MySQL-kysely toistaa hakutuloksia

Terve

Haen tietokannasta tietoa tälläisellä kyselyllä.

SELECT
   r.id, r.nimi AS rnimi, r.ainekset, t.nimi
FROM
   reseptit r,
   ruokatyypit t
WHERE
   r.tyyppi = t.id AND
   LOWER(r.nimi) LIKE '%...%' OR
   r.ainekset LIKE '%suola%' OR
   r.ainekset LIKE '%tomaatti%'

Kun LIKE-ehtoja erottaa OR, hakutuloksia on 24. Kun niitä erottaa AND, hakutuloksia on 3, joka on oikein: kaikista tuloksista löytyy sekä tomaatteja ja suolaa. OR:lla erotettuna pitäisi saada tässä tapauksessa myös kaikki tulokset, koska jokainen tietokannan rivi sisältää vähintään yhtä näistä. Koska vain yksi sisältää sekä tomaatteja ja suolaa, sen pitäisi olla ennen sitä, mikä sisältää vain esimerkiksi suolaa. Nyt kun tulokset toteuttavat useampia ehdoista, samoja rivejä tulee monta kertaa.

Lisäsin kentälle FULLTEXT-indeksin ja muutin kyselyä. BOOLEAN MODE päälle ja hakusanat tähtien sisään.

SELECT
   r.id, r.nimi AS rnimi, r.ainekset, t.nimi
FROM
   reseptit r,
   ruokatyypit t
WHERE
   r.tyyppi = t.id AND
   LOWER(r.nimi) LIKE '%%' OR
   MATCH(r.ainekset) AGAINST('*suola* *tomaatti*' IN BOOLEAN MODE)

Kysely hakee edelleen samat 24 riviä, jotka ylempänä ollut LIKE haki. Kyselyn järjestys on oikea (suolaa ja tomaatteja sisältävät ensimmäisenä, vain suolaa tai tomaatteja sisältävät sen jälkeen). Kumminkin jokaista hakutulosta on kahdeksan kertaa listassa.

Laita ne keskenään vaihtoehtoiset sulkuihin

WHERE r.tyyppi = t.id AND (
  LOWER(r.nimi) LIKE '%...%' OR
  r.ainekset LIKE '%suola%' OR
  r.ainekset LIKE '%tomaatti%'
)

Muutenhan tuota taulujen sitomiseen käyttämääsi ehtoa ei sovelleta kuin ensimmäisen ehdon kanssa. Eli käytännössähän olet kirjoittanut

WHERE ( r.tyyppi = t.id AND LOWER(r.nimi) LIKE '%...%') OR
r.ainekset LIKE '%suola%' OR
r.ainekset LIKE '%tomaatti%'

Tosin mielestäni vielä parempi olisi

SELECT
  r.id, r.nimi AS rnimi, r.ainekset, t.nimi
FROM
  reseptit r JOIN
  ruokatyypit t ON r.tyyppi = t.id
WHERE
  LOWER(r.nimi) LIKE '%...%' OR
  r.ainekset LIKE '%suola%' OR
  r.ainekset LIKE '%tomaatti%'

Kiitos.

FULLTEXT-haut ovat kuulemma suurilla datamäärillä nopeampia. Mitenköhän tämä mahtaisi onnistua? Omat datamäärät eivät ole niin suuria, mutta ne ovat kuulemma myös tarkempia ja muutenkin olisi hauska tietää miten se toimisi.

Macro kirjoitti:

Kyselyn järjestys on oikea

Kyselyssäsi ei ole ORDER BY -osaa, joten järjestys on tuurista kiinni.

Macro kirjoitti:

Kumminkin jokaista hakutulosta on kahdeksan kertaa listassa.

Ainakin ensimmäisen viestin kyselyssä on tuo ihan sama järjestysvirhe, josta Grez jo kertoi. Siitä huolimatta samaa tulosta ei kyllä pitäisi koskaan tulla monta kertaa. Et kai ole laittanut tietokantaan samaa reseptiä moneen kertaan eri ruokatyypeillä? Se on väärin.

Metabolix kirjoitti:

Kyselyssäsi ei ole ORDER BY -osaa, joten järjestys on tuurista kiinni.

En tiedä puhutko nyt versiosta jossa käytetään LIKE:ä, mutta FULLTEXT-hakujen kohdalla MySQL sanoo kyllä toisin.

http://dev.mysql.com/doc/refman/5.5/en/fulltext-natural-language.html:

When MATCH() is used in a WHERE clause, as in the example shown earlier, the rows returned are automatically sorted with the highest relevance first.

Metabolix kirjoitti:

Ainakin ensimmäisen viestin kyselyssä on tuo ihan sama järjestysvirhe, josta Grez jo kertoi. Siitä huolimatta samaa tulosta ei kyllä pitäisi koskaan tulla monta kertaa. Et kai ole laittanut tietokantaan samaa reseptiä moneen kertaan eri ruokatyypeillä? Se on väärin.

Grezin viimeisin kysely tuottaa toivotun tuloksen, vaikka tässäkin oikea järjestys taitaa tulla tuurilla. Keskimmäinen ei toimi yhtään sen paremmin kuin omanikaan, tulos on samanlainen kuin omani.

Jokaista reseptiä kohti on vain yksi tyyppi.

Macro kirjoitti:

Metabolix kirjoitti:

Kyselyssäsi ei ole ORDER BY -osaa, joten järjestys on tuurista kiinni.

En tiedä puhutko nyt versiosta jossa käytetään LIKE:ä, mutta FULLTEXT-hakujen kohdalla MySQL sanoo kyllä toisin.

Voi hyvä ihminen, luepa nyt edes oikesta aiheesta. Siteeraat sivua, joka alkaa "by default or with the IN NATURAL LANGUAGE MODE modifier". Manuaalin seuraava sivu taas sanoo, että boolean-haussa näin ei tapahdu ("boolean full-text searches – – do not automatically sort rows in order of decreasing relevance").

Macro kirjoitti:

Keskimmäinen ei toimi yhtään sen paremmin kuin omanikaan, tulos on samanlainen kuin omani.

Kuten Grez selvällä suomen kielellä kirjoitti, keskimmäinen versio onkin aivan sama kuin nykyinen koodisi – selventävästi vielä virheellisillä suluilla kirjoitettuna, jotta varmasti näkisit, missä vika on. Oikeasti, luetko näitä viestejä edes?

Macro kirjoitti:

Jokaista reseptiä kohti on vain yksi tyyppi.

Niin varmasti on, sen näen kyllä kyselystä. Kysyinkin, oletko varma, ettei kannassa ole useaa samanlaista reseptiä (eli vain id ja tyyppi eroavat). Muuta järkevää syytä toistuville riveille en nyt keksi, varsinkaan, jos oikein sulutettu kysely toimii oikein.

Nähtävästi luin manuaalin vääräältä sivulta lainatun kohdan, upsis. Ja jostain syystä tuo Grezin lause jäi välistä ja kopioin koodin suoraan tuosta kyselyyn ja testasin.

Ja olen varma, ettei kannassa ole useaa samanlaista reseptiä, joissa vain id ja tyyppi eroavat.

Macro kirjoitti:

Nyt kun tulokset toteuttavat useampia ehdoista, samoja rivejä tulee monta kertaa.

Tilanteet joissa samoja rivejä tulee monta kertaa. Mikä näillä riveillä on reseptit.id? Onko tuo id sama vaiko eri eri riveillä? Ja onhan tuo id AUTO_INCREMENT?

Vaikka monta ehtoa toteutuisi, yhden rivin pitäisi aina tulla vain kerran. MySQL:n toimintaa voi yksinkertaisesti kuvata niin, että ensin luodaan kaikki mahdolliset tulosrivit (tässä jokainen resepti yhdistettynä jokaiseen tyyppiin) ja sitten karsitaan ehtojen mukaan näistä suurin osa pois. Käytännössä toki operaatio tapahtuu paljon tehokkaammin, mutta lopputuloksen pitäisi olla tämä.

Kun haetaan useammasta taulusta, ilman mitään rajoituksia tulee n*m riviä. Eli jos tauluissa on vaikka 15 ja 20 riviä, niin tulokseen tulee 300 riviä. Erilaisilla ehdoilla saadaan sitten rajoitettua tulokseen tulevien rivien määrää.

Tässä puheena olevassa systeemissä on ilmeisesti 8 ruokatyyppiä. Kun pelkästään ruokaan relevantin tyypin hakeva sidos (t.id=r.tyyppi) tippuu pois, niin jokainen ruoka-taulun rivi tulee tietenkin kahdeksaan kertaan, vaikka niitä tyyppitaulun tietoja ei näytetäkään.

Edelleen suosittelen määrittelemään taulujen väliset sidokset JOIN .. ON rakenteella, jolloin WHERE-lauseeseen mahdollisesti tulevat kämmit ei vaikuta asiaan.

Kun kerran Grezin koodi toimii JOIN:n kanssa, niin käytän sitä. Kiitos vastauksista. Se ei kyllä järjestä hakutuloksia, mutta kyllä siihen jotain keksitään.

Tarkoitushan ei ollut sinänsä antaa mitään valmista koodia, vaan lähinnä antaa eväitä ymmärtää mitä tapahtuu ja miksi. Näillä eväillä sitten voisi korjata ongelman ja tulevatkin ongelmat ellei peräti saa niitä kokonaan vältettyä.

Järjestyksen saa yleensä tehtyä ORDER BY:llä. Voisi pisteyttää erilaiset osumat ja järjestää niiden summan perusteella.