Full-text index

MySQL Full-Text Search is a powerful feature for searching and matching text data efficiently. However, users may encounter limitations when dealing with short words due to the default configuration of the ft_min_word_len parameter. This parameter determines the minimum length of words to be indexed, and by default, it is set to a higher value.

Solution

To address the issue of short words not being indexed or retrieved, follow these steps:

1. Check Current Configuration

Before making any changes, it's important to check the current value of ft_min_word_len. You can do this by executing the following SQL query:

SHOW VARIABLES LIKE 'ft_min_word_len';

Take note of the current value, as you'll need it later.

2. Modify ft_min_word_len

Edit your MySQL configuration file (typically my.cnf or my.ini) and set the ft_min_word_len to 2. If you don't have access to the configuration file, you can also modify it dynamically using the following SQL command:

SET GLOBAL ft_min_word_len = 2;

3. Drop Existing Full-Text Indexes

To ensure the changes take effect, existing Full-Text indexes need to be dropped. Execute the following SQL commands for each affected table:

ALTER TABLE `_c_m_artists` DROP INDEX `name`;
ALTER TABLE `_c_m_tracks` DROP INDEX `title`;
ALTER TABLE `_c_m_albums` DROP INDEX `title`;

4. Re-Add Full-Text Indexes

After dropping the indexes, re-add them to the tables using the following SQL command:

ALTER TABLE `_c_m_artists` ADD FULLTEXT(`name`);
ALTER TABLE `_c_m_tracks` ADD FULLTEXT(`title`);
ALTER TABLE `_c_m_albums` ADD FULLTEXT(`title`);

Operating without full-text

In version 2.0.52 we made full-text indexing optional. To opt-out of this option, if you have a small database, visit `Admin -> Setting -> Setting -> Search` and set index-type to `index`.

if you have a big database, you should first find and drop old indexes, this queries might help ( You should still check the database manually to make sure indexes were not added under a different name ):

ALTER TABLE `_c_m_artists` DROP INDEX `name`;
ALTER TABLE `_c_m_tracks` DROP INDEX `title`;
ALTER TABLE `_c_m_albums` DROP INDEX `title`;

Then you have to run these queries to add indexes:

ALTER TABLE `_c_m_artists` ADD INDEX(`name`);
ALTER TABLE `_c_m_tracks` ADD INDEX(`title`);
ALTER TABLE `_c_m_albums` ADD INDEX(`title`);

And you also have to edit "~rkhm/api/app/config_user.php", find:

define( "fulltext_search", "fulltext" ); 

and change it to

define( "fulltext_search", "index" );