Fulltext Index in MySQL
- 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
- Reacties op deze tutorial
Relevantie bij een FULLTEXT zoekopdracht.
Nu we weten hoe de full-text index op een kolom werkt en op welke manier we deze index kunnen gebruiken, is het tijd om eens wat dieper in te gaan op full-text zoekopdrachten.Een groot voorbeeld van dit soort zoekopdrachten is namelijk dat de resultaten gesorteerd worden op relevantie. In tegenstelling tot queries met LIKE waarbij gewoon alle resultaten teruggegeven worden, krijg je nu de rijen in volgorde van relevantie terug. Hoe vaker het zoekwoord in een rij voorkomt, hoe hoger de relevantie van die rij.
De relevatie is een niet-negatieve waarde die van de volgende factoren afhankelijk is:
- Het aantal woorden in de gevonden rij
- Het aantal unieke woorden in die rij
- Het totaal aantal woorden in alle gevonden rijen
- Het aantal rijen dat een bepaald woord bevat
Voor het bepalen van de zoekscore wordt elk woord gewaardeerd aan de hand van zijn significatien in de tabel en de query. Een woord dat vaak voorkomt in een tabel heeft een lager gewicht dan een wordt dat maar enkele keren voorkomt. Deze gewichten worden vervolgens gecombineerd om de relevantie van een rij te bepalen.
Weergeven van de relevantie
Hoewel deze zoekscores eigenlijk helemaal geen nuttige waarde hebben en enkel gebruikt worden om de resultaten op relevantie te sorteren, is het soms leuk om deze waarden toch eens weer te geven.
Laten we eens kijken wat een full-text zoekopdracht op de inhoud naar het trefwoord 'database' voor zoekscores oplevert:
Code
1
2
3
4
5
2
3
4
5
SELECT
id,
MATCH(inhoud) AGAINST('database') AS score
FROM
artikelen
id,
MATCH(inhoud) AGAINST('database') AS score
FROM
artikelen
Het resultaat van deze query is als volgt:
Code
1
2
3
4
5
6
7
8
9
10
2
3
4
5
6
7
8
9
10
+----+------------------+
| id | score |
+----+------------------+
| 1 | 0.66266459031789 |
| 2 | 0 |
| 3 | 0 |
| 4 | 0 |
| 5 | 0.67003110026735 |
| 6 | 0 |
+----+------------------+
| id | score |
+----+------------------+
| 1 | 0.66266459031789 |
| 2 | 0 |
| 3 | 0 |
| 4 | 0 |
| 5 | 0.67003110026735 |
| 6 | 0 |
+----+------------------+
We zien dat blijkbaar alleen de rijen met id 1 en 5 een resultaat opleveren bij deze zoekopdracht. Dat is ook duidelijk te zien als we de WHERE clausule toevoegen:
Code
1
2
3
4
5
6
7
2
3
4
5
6
7
SELECT
*,
MATCH(inhoud) AGAINST('database') AS score
FROM
artikelen
WHERE
MATCH(inhoud) AGAINST('database')
*,
MATCH(inhoud) AGAINST('database') AS score
FROM
artikelen
WHERE
MATCH(inhoud) AGAINST('database')
Het resultaat is dan als volgt:
Code
1
2
3
4
5
6
2
3
4
5
6
+----+-------------------+------------------------------------------+------------------+
| id | titel | inhoud | score |
+----+-------------------+------------------------------------------+------------------+
| 5 | MySQL vs. YourSQL | In de volgende database vergelijking ... | 0.67003110026735 |
| 1 | MySQL Tutorial | DBMS staat voor DataBase ... | 0.66266459031789 |
+----+-------------------+------------------------------------------+------------------+
| id | titel | inhoud | score |
+----+-------------------+------------------------------------------+------------------+
| 5 | MySQL vs. YourSQL | In de volgende database vergelijking ... | 0.67003110026735 |
| 1 | MySQL Tutorial | DBMS staat voor DataBase ... | 0.66266459031789 |
+----+-------------------+------------------------------------------+------------------+
Zoals je ziet wordt het gevonden resultaat met de hoogste relevantie als eerste getoond. Daarna volgt de rest van de resultaten op aflopende relevantie.
We zien dat de resultaten met een relevantie van 0 niet weergegeven worden zodra je de WHERE clausule in de query opneemt. Een relevatien van 0 betekent dat er geen overeenkomsten zijn gevonden en dus worden deze rijen niet opgenomen in het resultaat van de zoekopdracht.
Het twee keer toepassen van de MATCH(...) AGAINST(...) combinatie in de query veroorzaakt geen extra overhead. De MySQL optimizer herkent namelijk dat deze twee bewerkingen identiek zijn en zal de full-text zoekopdracht daarom maar een keer uitvoeren.
Een string als zoekopdracht
Met de full-text zoekfunctie is het ook mogelijk om een string al zoekopdracht op te geven. Laten we eens kijken naar de volgende query:
Code
1
2
3
2
3
SELECT *
FROM artikelen
WHERE MATCH(inhoud) AGAINST('start mysql nooit')
FROM artikelen
WHERE MATCH(inhoud) AGAINST('start mysql nooit')
Het resultaat van deze query is het volgende:
Code
1
2
3
4
5
6
2
3
4
5
6
+----+------------------+------------------------------------------+
| id | titel | inhoud |
+----+------------------+------------------------------------------+
| 4 | 1001 MySQL tips | 1. Start MySQL nooit als root 2. ... |
| 6 | MySQL veiligheid | Als je MySQL goed geinstalleerd hebt ... |
+----+------------------+------------------------------------------+
| id | titel | inhoud |
+----+------------------+------------------------------------------+
| 4 | 1001 MySQL tips | 1. Start MySQL nooit als root 2. ... |
| 6 | MySQL veiligheid | Als je MySQL goed geinstalleerd hebt ... |
+----+------------------+------------------------------------------+
Misschien is dit niet helemaal wat je zou verwachten, aangezien je gezocht hebt naar 'start mysql nooit' en die string helemaal niet voorkomt in het tweede record.
Dit heeft echter te maken met de manier waarop MySQL full-text zoekopdrachten uitvoert. MySQL behandeld elke combinatie van tekens die een woord kunnen voorkomen (letters, cijfers en underscores) als een woord. Elk woord wordt apart opgezocht in de index en vervolgens wordt de zoekscore bepaald. Vervolgens wordt met behulp van al deze zoekscores de relevantie van een rij bepaald.
Dit heeft dus als gevolg dat ook rijen weergegeven worden waarin maar 1 woord uit de zoekopdracht in voorkomt. En dit is precies het punt waarom zoekresultaten gesorteerd worden op relevantie. Deze waarde zal namelijk voor het eerste record vele malen hoger zijn, dan voor het tweede gevonden record. Dat blijkt ook als we die relevantie tonen:
Code
1
2
3
4
5
6
2
3
4
5
6
+----+------------------+------------------------------------------+------------------+
| id | titel | inhoud | score |
+----+------------------+------------------------------------------+------------------+
| 4 | 1001 MySQL tips | 1. Start MySQL nooit als root 2. ... | 3.7399836498101 |
| 6 | MySQL veiligheid | Als je MySQL goed geinstalleerd hebt ... | 0.66266459031789 |
+----+------------------+------------------------------------------+------------------+
| id | titel | inhoud | score |
+----+------------------+------------------------------------------+------------------+
| 4 | 1001 MySQL tips | 1. Start MySQL nooit als root 2. ... | 3.7399836498101 |
| 6 | MySQL veiligheid | Als je MySQL goed geinstalleerd hebt ... | 0.66266459031789 |
+----+------------------+------------------------------------------+------------------+
MySQL bepaalt waar woorden beginnen en eindigen door te zoeken naar bepaalde tekens, zoals spaties, kommas en punten. Een apostrof in een zoekopdracht is ook geen probleem, zodra het er maar niet meer worden. Een woord als data'base wordt beschouwt als 1 woord, terwijl data''base als 2 woorden beschouwd wordt. Een apostrof aan het begin of eind van een woord, wordt tijdens een full-text zoekopdracht genegeerd: 'data'base' wordt dus geinterpreteerd als data'base.
