Mysql – MATCH AGAINST one character words returns empty rows with ft_min_word_len = 1

environment-variablesmy.cnfMySQLpattern matching

I've set ft_min_word_len = 1 to my.cnf file, also SHOW VARIABLES LIKE 'ft_min_word%' displays ft_min_word_len as 1. Then I've reloaded all database tables and it still doesn't search for one character words.

My table:

CREATE TABLE IF NOT EXISTS event (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(80) NOT NULL,
description TEXT,
time TIMESTAMP DEFAULT 0,
FULLTEXT(name)
);

My query:

SELECT event.id AS eid,
    event.name AS ename
FROM event
WHERE MATCH(event.name) AGAINST('+W*' IN BOOLEAN MODE)

And event name I'm searching for is W.W.W. + Pavel Fajt + Vladimir 518 @ Sono

What else could cause the problem please?

EDIT: I'm using InnoDB, sorry I haven't mention it before.

Best Answer

mysql> SHOW GLOBAL VARIABLES like 'ft_min_word_len';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| ft_min_word_len | 1     |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> SELECT event.id AS eid,     
              event.name AS ename 
       FROM event 
       WHERE MATCH(event.name) AGAINST('+W*' IN BOOLEAN MODE);
+-----+--------+
| eid | ename  |
+-----+--------+
|   1 | W.W.W. |
|   7 | W      |
|  12 | Wa     |
|  11 | Wat    |
|  10 | Wats   |
|   9 | Watso  |
|   8 | Watson |
+-----+--------+
7 rows in set (0.01 sec)

Maybe you are using InnoDB:

mysql> ALTER TABLE event ENGINE=InnoDB;
Query OK, 12 rows affected (0.51 sec)
Records: 12  Duplicates: 0  Warnings: 0

mysql> SELECT event.id AS eid,
       event.name AS ename 
       FROM event 
       WHERE MATCH(event.name) AGAINST('+W*' IN BOOLEAN MODE);
+-----+--------+
| eid | ename  |
+-----+--------+
|   8 | Watson |
|   9 | Watso  |
|  10 | Wats   |
|  11 | Wat    |
+-----+--------+
4 rows in set (0.00 sec)

Where the option to change is innodb_ft_min_token_size (ft_min_word_len is for MyISAM only).

YOUR QUERY

SELECT post.postid, post.attach FROM newbb_innopost AS post WHERE post.threadid = 51506;

At first glance, that query should only touches 1.1597% (62510 out of 5390146) of the table. It should be fast given the key distribution of threadid 51506.

REALITY CHECK

No matter which version of MySQL (Oracle, Percona, MariaDB) you use, none of them can fight to one enemy they all have in common : The InnoDB Architecture.

InnoDB Architecture

CLUSTERED INDEX

Please keep in mind that the each threadid entry has a primary key attached. This means that when you read from the index, it must do a primary key lookup within the ClusteredIndex (internally named gen_clust_index). In the ClusteredIndex, each InnoDB page contains both data and PRIMARY KEY index info. See my post Best of MyISAM and InnoDB for more info.

REDUNDANT INDEXES

You have a lot of clutter in the table because some indexes have the same leading columns. MySQL and InnoDB has to navigate through the index clutter to get to needed BTREE nodes. You should reduced that clutter by running the following:

ALTER TABLE newbb_innopost
    DROP INDEX threadid,
    DROP INDEX threadid_2,
    DROP INDEX threadid_visible_dateline,
    ADD INDEX threadid_visible_dateline_index (`threadid`,`visible`,`dateline`,`userid`)
;

Why strip down these indexes ?

The first three indexes start with threadid
threadid_2 and threadid_visible_dateline start with the same three columns
threadid_visible_dateline does not need postid since it's the PRIMARY KEY and it's embedded

BUFFER CACHING

The InnoDB Buffer Pool caches data and index pages. MyISAM only caches index pages.

Just in this area alone, MyISAM does not waste time caching data. That's because it's not designed to cache data. InnoDB caches every data page and index page (and its grandmother) it touches. If your InnoDB Buffer Pool is too small, you could be caching pages, invalidating pages, and removing pages all in one query.

TABLE LAYOUT

You could shave of some space from the row by considering importthreadid and importpostid. You have them as BIGINTs. They take up 16 bytes in the ClusteredIndex per row.

You should run this

SELECT importthreadid,importpostid FROM newbb_innopost PROCEDURE ANALYSE();

This will recommend what data types these columns should be for the given dataset.

CONCLUSION

MyISAM has a lot less to contend with than InnoDB, especially in the area of caching.

While you revealed the amount of RAM (32GB) and the version of MySQL (Server version: 10.0.12-MariaDB-1~trusty-wsrep-log mariadb.org binary distribution, wsrep_25.10.r4002), there are still other pieces to this puzzle you have not revealed

The InnoDB settings
The Number of Cores
Other settings from my.cnf

If you can add these things to the question, I can further elaborate.

UPDATE 2014-08-28 11:27 EDT

You should increase threading

innodb_read_io_threads = 64
innodb_write_io_threads = 16
innodb_log_buffer_size = 256M

I would consider disabling the query cache (See my recent post Why query_cache_type is disabled by default start from MySQL 5.6?)

query_cache_size = 0

I would preserve the Buffer Pool

innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1

Increase purge threads (if you do DML on multiple tables)

innodb_purge_threads = 4

GIVE IT A TRY !!!

Mysql – How to match all words of one column against words in another column

For a quick and dirty solution - not 100% accurate due to some full-text search specifics but will get most of the matches - add a FULLTEXT key to A and then use a join with be with MATCH AGAINST as the join condition. Since MATCH AGAINST cannot use a a non-constant argument, you will have to simulate the join with a cursor in a stored procedure. Below is a fully-functional tested example:

create table a (id int not null primary key, 
company_name text, fulltext key(company_name)) engine=myisam;
create table b (id int not null primary key, company_name1 text);
insert into a values(1,'dog kitten'),(2,'spoon fork'),
  (3,'fish crab'),(4,'dog mouse'),(5,'noise mouse'),
(6,'kitten dog'),(7,'noise one'),(8,'noise two'),
  (9,'noise three'),(10,'noise four'),(11,'noise five');
insert into b values(1,'dog mouse kitten'),
(2,'spoon knife fork'),(3,'fish sea crab');

drop procedure if exists ft_match;
delimiter //
create procedure ft_match()
language sql
deterministic
sql security definer
begin
    declare v_id int;
    declare v_company_name1 text;
    declare v_finished int;
  declare c cursor for select * from b;
    declare continue handler for not found set v_finished=1;
  delete from results;
  open c;
c_loop: loop
    fetch c into v_id,v_company_name1;
    if v_finished then
        leave c_loop;
    end if;
    insert into results select v_id,v_company_name1,a.id,a.company_name
     from a where match (a.company_name) against (v_company_name1 in boolean mode);
    end loop c_loop;
    close c;
    select * from results;
end//
delimiter ;

create table results (a_id int not null, a_company_name text, 
b_id int not null, b_company_name text);
call ft_match();

More info on full text keys in the MySQL manual at https://dev.mysql.com/doc/refman/5.5/en/fulltext-search.html

This is suitable for relatively small tables and in circumstances where high latency is acceptable. For better performance on large datasets and perfect accuracy, you will need to implement some form of external full text indexing.