Werking van de FULLTEXT index
Een full-text index is een index net als alle andere. Het is vergelijkbaar met bijvoorbeeld de index achter in een boek en biedt de database zo de mogelijkheid om gegevens sneller op te zoeken. Het grote voordeel van een index is namelijk dat niet de hele tabel doorzocht hoeft te worden op zoek naar een bepaald woord of combinatie van woorden.
De werking van een FULLTEXT index
Zoals al eerder gezegd wordt een full-text index dus gebruikt om kolommen waarin stukken tekst opgeslagen zijn te indexeren. Om die reden is een full-text index dus enkel aan te brengen op kolommen van het type CHAR, VARCHAR of TEXT.
Het indexeren van de gegevens verloopt anders dan bij andere indexen. In plaats van het indexeren van de hele waarde uit een veld, worden bij een full-text index alle woorden uit een tekst apart geindexeerd. Als voorwaarde voor het indexeren van een woord wordt een minimale lengte van 4 tekens gehanteerd.
Als er nu een full-text zoekopdracht uitgevoerd wordt, wordt deze index gebruikt om te kijken of er overeenkomende woorden te vinden zijn en zo ja, in welk record deze woorden dan te vinden zijn. Deze manier van zoeken voorkomt dus dat elk record uit de hele tabel doorzocht moet worden en levert een grote snelheidswinst op.
Een tweede reden waarom snelheidswinst geboekt kan worden is het feit dat enkel woorden van 4 tekens of langer geindexeerd worden. In een full-text zoekopdracht worden woorden van 3 of minder tekens dan ook genegeerd en wordt het proces zo versneld.
Het aanbrengen van een full-text index
Zoals ik hierboven al vermelde is een full-text enkel aan te brengen op een CHAR, VARCHAR of TEXT kolom in een tabel met de MyISAM storage engine. De volgende query gebruik je om een FULLTEXT index aan te brengen op de 'inhoud' kolom:
Omdat we straks ook in de titel willen zoeken, brengen we ook een full-text index aan op de titel kolom. Het is daarnaast ook mogelijk om een full-text index op meerdere kolommen aan te brengen.
Let er in dit geval wel op dat MySQL gebruik maakt van left-most prefixing en je deze index dus niet kunt gebruiken voor een zoekopdracht op enkel de 'inhoud' kolom. Kijk voor meer informatie hierover in mijn tutorial over MySQL Indexes.
Via phpMyAdmin kun je een full-text index op een kolom aanbrengen door op het
knopje achter de betreffende kolom te klikken.Zoeken met behulp van de FULLTEXT index
Om de full-text index te gebruiken bij het zoeken in de database maken we gebruik van een aparte syntax die er als volgt uit ziet:
2
3
FROM tabel
WHERE MATCH(kolom) AGAINST('zoekwoord')
Deze query doorzoekt de full-text index van de kolom genoemd in MATCH() naar het voorkomen van het zoekwoord gegeven in AGAINST().
Laten we eens zien wat voor resultaten we krijgen als we de LIKE query van de vorige pagina omschrijven naar een full-text zoekopdracht.
2
3
FROM artikelen
WHERE MATCH(inhoud) AGAINST('database')
Dit geeft het volgende resultaat:
2
3
4
5
6
| id | titel | inhoud |
+----+-------------------+------------------------------------------+
| 5 | MySQL vs. YourSQL | In de volgende database vergelijking ... |
| 1 | MySQL Tutorial | DBMS staat voor DataBase ... |
+----+-------------------+------------------------------------------+
Precies hetzelfde resultaat als bij de query met LIKE dus. Als we echter kijken naar de EXPLAIN van deze query zien we dat er inderdaad gebruik is gemaakt van de index:
2
3
4
5
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+----------+---------------+--------+---------+------+------+-------------+
| 1 | SIMPLE | artikelen | fulltext | inhoud | inhoud | 0 | | 1 | Using where |
+----+-------------+-----------+----------+---------------+--------+---------+------+------+-------------+
Eigenschappen van een full-text zoekopdracht
Full-text indexen worden over het algemeen gebruikt om grotere stukken tekst, zoals bijvoorbeeld kranten artikelen of content van webpaginas. Om dit soort zoekopdrachten te optimaliseren heeft de full-text functionaliteit van MySQL een aantal belangrijke eigenschappen:
Gedeeltelijke woorden worden uitgesloten van de zoekopdracht.
Woorden van 3 tekens of korter worden uitgesloten van de zoekopdracht.
Woorden die in meer dan de helft van alle rijen in een tabel voorkomen, worden uitgesloten.
Woorden met daarin een - worden behandeld als 2 aparte woorden.
Rijen worden gesorteerd op relevantie. Hoe vaker een zoekwoord voorkomt, hoe hoger de relevantie.
Woorden in de lijst met stopwoorden, zijn uitgesloten van een zoekopdracht.
Full-text zoekopdrachten zijn standaard niet hoofdletter gevoelig.
Een aantal van deze eigenschappen heb ik al eerder genoemd, enkele echter nog niet. Dat woorden die in meer dan 50% van de rijen in een tabel voorkomen genegeerd worden tijdens een full-text zoekopdracht heeft te maken met het feit dat je die records dan waarschijnlijk helemaal niet wilt zien. Als de meeste records in de database 'MySQL' bevatten en je enkel geinteresseerd bent in 'MySQL veiligheid' wil je al die andere records met 'MySQL' erin waarschijnlijk niet zien.
Het voorbeeld is te zien in deze query:
2
3
FROM artikelen
WHERE MATCH(titel) AGAINST('MySQL')
Deze geeft namelijk een lege resultaat-set terug.
Resultaten van een full-text zoekopdracht worden automatisch gesorteerd op aflopende relevantie. Hoe deze relevantie precies bepaald wordt en wat je ermee kunt doen, zal ik later in deze tutorial bespreken.
Tenslotte is er nog de lijst met stopwoorden die uitgesloten worden van een full-text zoekopdracht. Deze lijst is gebasseerd op de Engelse taal en zal dus niet veel invloed hebben op Nederlandse teksten. Voor de volledigheid is de lijst hier te vinden.
FULLTEXT index op meerdere kolommen
Zoals ik eerder al vertelde is het ook mogelijk om een full-text index aan te brengen op meerdere kolommen. De inhoud van deze kolommen zal dan geindexeerd worden in dezelfde index en kan dus gebruikt worden om in beide kolommen tegelijk te zoeken.
Stel dat we een index (titel, inhoud) hebben, dan zouden we met de volgende query van die index gebruik kunnen maken:
2
3
FROM artikelen
WHERE MATCH(titel, inhoud) AGAINST('tutorial')
Het resultaat van deze query is als volgt:
2
3
4
5
6
| id | titel | inhoud |
+----+---------------------+----------------------------------------+
| 1 | MySQL Tutorial | DBMS staat voor DataBase ... |
| 3 | MySQL Optimaliseren | In deze tutorial zal ik laten zien ... |
+----+---------------------+----------------------------------------+
We zien dat er nu zowel in de titel als in de inhoud gezocht wordt naar het trefwoord 'tutorial'. Ook laat het resultaat van EXPLAIN ons zien dat inderdaad de index (titel, inhoud) gebruikt wordt.
Inhoudsopgave
- Inleiding
- Zoeken in een MySQL database
- Werking van de FULLTEXT index
- Relevantie bij een FULLTEXT zoekopdracht.
- Boolean FULLTEXT zoekopdrachten
- FULLTEXT Query Expansion
- Slotwoord en referenties