MySQL FullText Indexes

RTFM!

Posted by debuss-a on 23rd Jun 2018

Learning thai is a pain in the arse, especially when time comes to find a dictionary.

I was struggling finding a good one on android, and I thought I did with the Londgo application.
The good point is that among the translations, there is sometimes a Thai - English - French dictionary section with thai words written in simplified thai phonetic, whereas all other dictionaries uses thai alphabet.
The bad point is that this section only appears if the french word looks like the english word.

For instance, if you look for the word "avion" (=plane), you will not get the Thai - English - French section, because "avion" is not closed to any english word.
However, if you look for "plane", you will get the Thai - English - French section because in french we have words like "aéroplane".

Not really handy...

The Thai - English - French dictionary section is powered by a dictionary called Volubilis.
Then no more excuses, here is a chance for me to create my first android app using this multilingual thai database !

Before any android coding, first thing to do was the API to fetch words from the dictionary.
I downloaded the latest version of the dictionary (106.000 entries as of today) and imported all a database.
This was a good time to have a look at the MySQL InnoDB FULLTEXT Indexes.

Note : some words have translation in english, but not in french, and vice versa...

LIKE query

First thing that come in mind would be to use the LIKE clause because it is easy to use.
Let's see if we look for "avion" :

SELECT `easythai`, `en`, `fr`
FROM `dictionary`
WHERE (
    `fr` LIKE "%avion%"
    OR `en` LIKE "%avion%"
    OR `th` LIKE "%avion%"
)
AND `en` != ""
AND `th` != "";

The query fetches 43 results in 0.16s.

+-----------------------------------+--------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+
| easythai                          | en                                                                                                           | fr                                                                                                                                      |
+-----------------------------------+--------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+
| akatsayan                         | aircraft ; airplane ; aeroplane ; airship                                                                    | avion [m] ; aéronef [m] (vx)                                                                                                            |
| baep khreuangbin                  | aircraft type                                                                                                | type d'avion [m] ; type d'appareil [m] ; appareil [m]                                                                                   |
| bon khreuangbin                   | aboard aircraft aboard                                                                                       | à bord d'un avion                                                                                                                       |
| doi khreuangbin                   | by plane                                                                                                     | en avion                                                                                                                                |
| hun khreuangbin                   | model aircraft                                                                                               | maquette d'avion [f] ; modèle d'avion [m]                                                                                               |
| ji khreuangbin                    | hijack an aeroplane                                                                                          | détourner un avion                                                                                                                      |
| jongtua khreuangbin               | book a flight                                                                                                | réserver un billet d'avion                                                                                                              |
| kheun khreuang                    | board a plane ; get on a plane                                                                               | monter à bord d'un avion ; embarquer à bord d'un avion ; monter dans un avion ; embarquer                                               |
| kheun khreuangbin                 | board a plane ; get on a plane                                                                               | monter à bord d'un avion ; embarquer à bord d'un avion ; monter dans un avion ; embarquer                                               |
| khreuangbin                       | airplane ; plane ; aircraft ; aeroplane ; airship                                                            | avion [m] ; aéronef [m]  (vx) ; aéroplane [m] (vx) ; appareil [m]                                                                       |
| khreuangbin Aebas                 | Airbus                                                                                                       | avion Airbus [m] ; Airbus [m]                                                                                                           |
| khreuangbin ai phon               | jet ; jet plane                                                                                              | avion à réaction [m] ; jet [m]                                                                                                          |
| khreuangbin baiphat               | propeller plane                                                                                              | avion à hélice [m]                                                                                                                      |
| khreuangbin Boing                 | Boeing                                                                                                       | avion Boeing [m] ; Boeing [m]                                                                                                           |
| khreuangbin doisan                | passenger aircraft ; passenger plane                                                                         | avion de transport de passagers [m]                                                                                                     |
| khreuangbin jet                   | jet plane ; jetliner ; jet                                                                                   | avion à réaction [m] ; jet [m] (anglic.)                                                                                                |
| khreuangbin khonlong              | transport plane                                                                                              | avion de transport [m]                                                                                                                  |
| khreuangbin khonsong              | transport plane                                                                                              | avion de transport [m]                                                                                                                  |
| khreuangbin khonsong sinkha       | cargo plane                                                                                                  | avion-cargo [m]                                                                                                                         |
| khreuangbin khonsong thang thahan | military transport aircraft ; military cargo aircraft                                                        | avion de transport militaire [m]                                                                                                        |
| khreuangbin khwamreo neua sieng   | supersonic aircraft                                                                                          | avion supersonique [m]                                                                                                                  |
| khreuangbin kradat                | paper plane ; paper aeroplane                                                                                | avion en papier [m]                                                                                                                     |
| khreuangbin mao lam               | charter plane ; charter flight                                                                               | charter [m] (anglic.) ; avion nolisé [m]                                                                                                |
| khreuangbin prajanban             | fighter                                                                                                      | avion de chasse [m] ; chasseur [m] ; avion de combat [m]                                                                                |
| khreuangbin rop                   | fighter aircraft ; fighter plane ; fighter                                                                   | avion de combat [m]                                                                                                                     |
| khreuangbin suantua               | private jet                                                                                                  | avion privé [m]                                                                                                                         |
| khreuangbin thale                 | seaplane ; hydrplane                                                                                         | hydravion [m]                                                                                                                           |
| khreuangyon akatsayan             | aircraft engine                                                                                              | moteur d'avion [m]                                                                                                                      |
| lam                               | [classif. : boats, ships, watercraft, airplanes, long round hollow objects (bamboo, stems, sugarcanes, ...)] | [classif. : bateaux, embarcations, avions, fusées, objets arrondis, allongés et creux (bambous, cannes à sucre, tiges, troncs  ...)]    |
| long khreuangbin                  | alight from a plane ; step down from a plane                                                                 | débarquer d'un avion ; descendre d'un avion                                                                                             |
| nakbin                            | pilot ; aviator ; flyer                                                                                      | pilote [m] ; pilote d'avion [m] ; aviateur [m]                                                                                          |
| nakbin phuchuay                   | co-pilot (of a plane)                                                                                        | copilote (avion) [m]                                                                                                                    |
| nang khreuangbin pai ...          | go to ... (destination) by plane                                                                             | aller à ... (destination) en avion                                                                                                      |
| phuphalit khreuangbin             | aircraft manufacturer                                                                                        | avionneur [m] ; constructeur d'avions [m]                                                                                               |
| pik khreuangbin                   | wing (of a plane)                                                                                            | aile d'avion [f] ; aile [f]                                                                                                             |
| reua banthuk khreuangbin          | aircarft carrier                                                                                             | porte-avions [m]                                                                                                                        |
| reua bin                          | plane ; aircraft ; aeroplane ; airplane ; airship ; seaplane                                                 | avion [m]                                                                                                                               |
| reua bin thale                    | helicopter carrier                                                                                           | hydravion [m]                                                                                                                           |
| seu tua khreuang bin              | buying plane tickets                                                                                         | acheter un billet d'avion                                                                                                               |
| thang akat                        | by air ; air                                                                                                 | aérien ; par avion ; par air                                                                                                            |
| tok khreuang                      | miss the flight                                                                                              | rater l'avion ; rater son vol ; manquer l'avion                                                                                         |
| tok khreuangbin                   | miss the flight                                                                                              | rater l'avion ; rater son vol ; manquer l'avion                                                                                         |
| tua khreuangbin                   | plane ticket ; air ticket                                                                                    | billet d'avion [m]                                                                                                                      |
+-----------------------------------+--------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+
43 rows in set (0.11 sec)

That's not really efficient. Moreover I have some problems with LIKE query :

  • It only search for 1 column (the fr one), if I want to check th and en as well then I need to add more clauses resulting in bad readability
  • As mentioned upper, some words don't have translation for french or english so you might need to add filters where en and fr are not empty
  • Heavier code upstream to determine if we need to search the word for french and/or english and/or match
  • Imagine the query time if the table add 2 million rows

MATCH AGAINST query

In our case, FullText Indexes is exactly what we need as we can simply search for a word in all 3 columns (th, en and fr).
Also, much more efficient on query time, let's see :

SELECT `easythai`, `en`, `fr`
FROM `dictionary`
WHERE MATCH (`th`, `en`, `fr`) AGAINST ('avion')
AND `fr` != ""
AND `en` != ""
AND `th` != "";

The query fetches 38 results in 0.00s.

+-----------------------------------+--------------------------------------------------------------+-----------------------------------------------------------------------------------------------+
| easythai                          | en                                                           | fr                                                                                            |
+-----------------------------------+--------------------------------------------------------------+-----------------------------------------------------------------------------------------------+
| kheun khreuang                    | board a plane ; get on a plane                               | monter à bord d'un avion ; embarquer à bord d'un avion ; monter dans un avion ; embarquer     |
| kheun khreuangbin                 | board a plane ; get on a plane                               | monter à bord d'un avion ; embarquer à bord d'un avion ; monter dans un avion ; embarquer     |
| hun khreuangbin                   | model aircraft                                               | maquette d'avion [f] ; modèle d'avion [m]                                                     |
| khreuangbin prajanban             | fighter                                                      | avion de chasse [m] ; chasseur [m] ; avion de combat [m]                                      |
| long khreuangbin                  | alight from a plane ; step down from a plane                 | débarquer d'un avion ; descendre d'un avion                                                   |
| tok khreuang                      | miss the flight                                              | rater l'avion ; rater son vol ; manquer l'avion                                               |
| tok khreuangbin                   | miss the flight                                              | rater l'avion ; rater son vol ; manquer l'avion                                               |
| akatsayan                         | aircraft ; airplane ; aeroplane ; airship                    | avion [m] ; aéronef [m] (vx)                                                                  |
| baep khreuangbin                  | aircraft type                                                | type d'avion [m] ; type d'appareil [m] ; appareil [m]                                         |
| bon khreuangbin                   | aboard aircraft aboard                                       | à bord d'un avion                                                                             |
| doi khreuangbin                   | by plane                                                     | en avion                                                                                      |
| ji khreuangbin                    | hijack an aeroplane                                          | détourner un avion                                                                            |
| jongtua khreuangbin               | book a flight                                                | réserver un billet d'avion                                                                    |
| khreuangbin                       | airplane ; plane ; aircraft ; aeroplane ; airship            | avion [m] ; aéronef [m]  (vx) ; aéroplane [m] (vx) ; appareil [m]                             |
| khreuangbin Aebas                 | Airbus                                                       | avion Airbus [m] ; Airbus [m]                                                                 |
| khreuangbin ai phon               | jet ; jet plane                                              | avion à réaction [m] ; jet [m]                                                                |
| khreuangbin baiphat               | propeller plane                                              | avion à hélice [m]                                                                            |
| khreuangbin Boing                 | Boeing                                                       | avion Boeing [m] ; Boeing [m]                                                                 |
| khreuangbin doisan                | passenger aircraft ; passenger plane                         | avion de transport de passagers [m]                                                           |
| khreuangbin jet                   | jet plane ; jetliner ; jet                                   | avion à réaction [m] ; jet [m] (anglic.)                                                      |
| khreuangbin khonlong              | transport plane                                              | avion de transport [m]                                                                        |
| khreuangbin khonsong              | transport plane                                              | avion de transport [m]                                                                        |
| khreuangbin khonsong sinkha       | cargo plane                                                  | avion-cargo [m]                                                                               |
| khreuangbin khonsong thang thahan | military transport aircraft ; military cargo aircraft        | avion de transport militaire [m]                                                              |
| khreuangbin khwamreo neua sieng   | supersonic aircraft                                          | avion supersonique [m]                                                                        |
| khreuangbin kradat                | paper plane ; paper aeroplane                                | avion en papier [m]                                                                           |
| khreuangbin mao lam               | charter plane ; charter flight                               | charter [m] (anglic.) ; avion nolisé [m]                                                      |
| khreuangbin rop                   | fighter aircraft ; fighter plane ; fighter                   | avion de combat [m]                                                                           |
| khreuangbin suantua               | private jet                                                  | avion privé [m]                                                                               |
| khreuangyon akatsayan             | aircraft engine                                              | moteur d'avion [m]                                                                            |
| nakbin                            | pilot ; aviator ; flyer                                      | pilote [m] ; pilote d'avion [m] ; aviateur [m]                                                |
| nakbin phuchuay                   | co-pilot (of a plane)                                        | copilote (avion) [m]                                                                          |
| nang khreuangbin pai ...          | go to ... (destination) by plane                             | aller à ... (destination) en avion                                                            |
| pik khreuangbin                   | wing (of a plane)                                            | aile d'avion [f] ; aile [f]                                                                   |
| reua bin                          | plane ; aircraft ; aeroplane ; airplane ; airship ; seaplane | avion [m]                                                                                     |
| seu tua khreuang bin              | buying plane tickets                                         | acheter un billet d'avion                                                                     |
| thang akat                        | by air ; air                                                 | aérien ; par avion ; par air                                                                  |
| tua khreuangbin                   | plane ticket ; air ticket                                    | billet d'avion [m]                                                                            |
+-----------------------------------+--------------------------------------------------------------+-----------------------------------------------------------------------------------------------+
38 rows in set (0.00 sec)

Indeed, that is much much much efficient that a basic LIKE query !

Wait, 38 rows ? We had like 43 rows with the LIKE query...
Indeed, we lost some rows, for example this one :

| lam | [classif. : boats, ships, watercraft, airplanes, long round hollow objects (bamboo, stems, sugarcanes, ...)] | [classif. : bateaux, embarcations, avions, fusées, objets arrondis, allongés et creux (bambous, cannes à sucre, tiges, troncs  ...)] |

Because the word here is "avions" with an "s" at the end.
Can be fixed with a search on "avion avions" :

SELECT `easythai`, `en`, `fr`
FROM `dictionary`
WHERE MATCH (`th`, `en`, `fr`) AGAINST ('avion avions')
AND `fr` != ""
AND `en` != ""
AND `th` != "";
+-----------------------------------+--------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+
| easythai                          | en                                                                                                           | fr                                                                                                                                      |
+-----------------------------------+--------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+
| kheun khreuang                    | board a plane ; get on a plane                                                                               | monter à bord d'un avion ; embarquer à bord d'un avion ; monter dans un avion ; embarquer                                               |
| kheun khreuangbin                 | board a plane ; get on a plane                                                                               | monter à bord d'un avion ; embarquer à bord d'un avion ; monter dans un avion ; embarquer                                               |
| hun khreuangbin                   | model aircraft                                                                                               | maquette d'avion [f] ; modèle d'avion [m]                                                                                               |
| khreuangbin prajanban             | fighter                                                                                                      | avion de chasse [m] ; chasseur [m] ; avion de combat [m]                                                                                |
| long khreuangbin                  | alight from a plane ; step down from a plane                                                                 | débarquer d'un avion ; descendre d'un avion                                                                                             |
| tok khreuang                      | miss the flight                                                                                              | rater l'avion ; rater son vol ; manquer l'avion                                                                                         |
| tok khreuangbin                   | miss the flight                                                                                              | rater l'avion ; rater son vol ; manquer l'avion                                                                                         |
| lam                               | [classif. : boats, ships, watercraft, airplanes, long round hollow objects (bamboo, stems, sugarcanes, ...)] | [classif. : bateaux, embarcations, avions, fusées, objets arrondis, allongés et creux (bambous, cannes à sucre, tiges, troncs  ...)]    |
| phuphalit khreuangbin             | aircraft manufacturer                                                                                        | avionneur [m] ; constructeur d'avions [m]                                                                                               |
| reua banthuk khreuangbin          | aircarft carrier                                                                                             | porte-avions [m]                                                                                                                        |
| akatsayan                         | aircraft ; airplane ; aeroplane ; airship                                                                    | avion [m] ; aéronef [m] (vx)                                                                                                            |
| baep khreuangbin                  | aircraft type                                                                                                | type d'avion [m] ; type d'appareil [m] ; appareil [m]                                                                                   |
| bon khreuangbin                   | aboard aircraft aboard                                                                                       | à bord d'un avion                                                                                                                       |
| doi khreuangbin                   | by plane                                                                                                     | en avion                                                                                                                                |
| ji khreuangbin                    | hijack an aeroplane                                                                                          | détourner un avion                                                                                                                      |
| jongtua khreuangbin               | book a flight                                                                                                | réserver un billet d'avion                                                                                                              |
| khreuangbin                       | airplane ; plane ; aircraft ; aeroplane ; airship                                                            | avion [m] ; aéronef [m]  (vx) ; aéroplane [m] (vx) ; appareil [m]                                                                       |
| khreuangbin Aebas                 | Airbus                                                                                                       | avion Airbus [m] ; Airbus [m]                                                                                                           |
| khreuangbin ai phon               | jet ; jet plane                                                                                              | avion à réaction [m] ; jet [m]                                                                                                          |
| khreuangbin baiphat               | propeller plane                                                                                              | avion à hélice [m]                                                                                                                      |
| khreuangbin Boing                 | Boeing                                                                                                       | avion Boeing [m] ; Boeing [m]                                                                                                           |
| khreuangbin doisan                | passenger aircraft ; passenger plane                                                                         | avion de transport de passagers [m]                                                                                                     |
| khreuangbin jet                   | jet plane ; jetliner ; jet                                                                                   | avion à réaction [m] ; jet [m] (anglic.)                                                                                                |
| khreuangbin khonlong              | transport plane                                                                                              | avion de transport [m]                                                                                                                  |
| khreuangbin khonsong              | transport plane                                                                                              | avion de transport [m]                                                                                                                  |
| khreuangbin khonsong sinkha       | cargo plane                                                                                                  | avion-cargo [m]                                                                                                                         |
| khreuangbin khonsong thang thahan | military transport aircraft ; military cargo aircraft                                                        | avion de transport militaire [m]                                                                                                        |
| khreuangbin khwamreo neua sieng   | supersonic aircraft                                                                                          | avion supersonique [m]                                                                                                                  |
| khreuangbin kradat                | paper plane ; paper aeroplane                                                                                | avion en papier [m]                                                                                                                     |
| khreuangbin mao lam               | charter plane ; charter flight                                                                               | charter [m] (anglic.) ; avion nolisé [m]                                                                                                |
| khreuangbin rop                   | fighter aircraft ; fighter plane ; fighter                                                                   | avion de combat [m]                                                                                                                     |
| khreuangbin suantua               | private jet                                                                                                  | avion privé [m]                                                                                                                         |
| khreuangyon akatsayan             | aircraft engine                                                                                              | moteur d'avion [m]                                                                                                                      |
| nakbin                            | pilot ; aviator ; flyer                                                                                      | pilote [m] ; pilote d'avion [m] ; aviateur [m]                                                                                          |
| nakbin phuchuay                   | co-pilot (of a plane)                                                                                        | copilote (avion) [m]                                                                                                                    |
| nang khreuangbin pai ...          | go to ... (destination) by plane                                                                             | aller à ... (destination) en avion                                                                                                      |
| pik khreuangbin                   | wing (of a plane)                                                                                            | aile d'avion [f] ; aile [f]                                                                                                             |
| reua bin                          | plane ; aircraft ; aeroplane ; airplane ; airship ; seaplane                                                 | avion [m]                                                                                                                               |
| seu tua khreuang bin              | buying plane tickets                                                                                         | acheter un billet d'avion                                                                                                               |
| thang akat                        | by air ; air                                                                                                 | aérien ; par avion ; par air                                                                                                            |
| tua khreuangbin                   | plane ticket ; air ticket                                                                                    | billet d'avion [m]                                                                                                                      |
+-----------------------------------+--------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------+
41 rows in set (0.00 sec)

But still missing some rows...
At this point I don't know exactly how to make the request to get all rows as the LIKE query, there is a probably a way but I didn't take the time yet to look about it.

For now, the result is satisfying for my needs.

Next step : The Vocab API !