Can underscore be forced as a word splitter without a full-text parser plugin?

Question

Is there any way in MySQL to force underscore to become a word splitter character (like space or dash) without having to use a fulltext parser plugin?

If not, does such a plugin already exist or do I have to learn to make my own?

Answer 1

I have never heard of such a plugin. Sure, you could make your own plugin.

However, I have a rather wild suggestion if you can afford the diskspace.

Let's take an example table:

CREATE TABLE mydb.mytable
(
    id int not null auto_increment,
    txt text,
    primary key (id),
    fulltext txt (txt)
) ENGINE=MyISAM;

Let's say a table like this is populated already.

Suppose you want to index any words such as

• abc_def_ghi
• innodb_file_per_table
• key_buffer_size

You may want to create a separate column with the underscores replaced with blanks. You will have to perform the following conversion

CREATE TABLE mydb.mytable_new LIKE mydb.mytable;
ALTER TABLE mydb.mytable_new ADD txtsrch TEXT;
ALTER TABLE mydb.mytable_new ADD FULLTEXT txt_txtsrtch (txt,txtsrch);
ALTER TABLE mydb.mytable_new DROP INDEX txt;
INSERT INTO mydb.mytable_new (id,txt,txtsrch)
SELECT id,txt,REPLACE(txt,'_',' ') FROM mydb.mytable;
ALTER TABLE mydb.mytable RENAME mydb.mytable_old;
ALTER TABLE mydb.mytable_new RENAME mydb.mytable;

Once done, the table should look like this

CREATE TABLE mydb.mytable
(
    id int not null auto_increment,
    txt text,
    txsrch text,
    primary key (id),
    fulltext txtsrch (txt,txtsrch)
) ENGINE=MyISAM;

This will index the data both ways. Consequently, this blows up the table and index files in size.

You will have to do searching with the following clause

MATCH(txt,txtsrch) AGAINST ...

Please perform this on a test server, look at the increased size of the new table and index. You can decide whether this table layout provides the data you want with tokens separated.

Give it a Try !!!