blog.dev001.net

As I’ve written in my previous post “1/3 Implementing an AutoSuggest feature using MySQL fulltext indices”, it’s possible to use the MySQL/MyISAM full-text index to extract search words for an AutoSuggest feature with great performance (because the index tree is used actually). This tool, called myisam_suggest, is my first implementation of this.

Download

Here: myisam_suggest.c

How to compile

1. Download the MySQL sources. If you have a MySQL server running, choose the same version or at least major version. Please note that you won’t have to replace your MySQL by a manually compiled one — myisam_suggest is only an external tool, but it needs the MySQL sources to access the MyISAM files directly.
2. Extract the sources, ./configure, make (no make install required).
3. Put the downloaded myisam_suggest.c into the “myisam” (for 5.0) or “storage/myisam” (5.1) directory.
4. Compile and link myisam_suggest exactly as the make process has compiled/linked myisam_ftdump. In my case, the commands are (in the MyISAM directory):
   gcc -DMAP_TO_USE_RAID -I. -I. -I../include -I../include -I../include    -DDBUG_OFF  -DDBUG_OFF -O -MT mi_dbug.o -MD -MP -MF ".deps/myisam_ftdump.Tpo" -c -o myisam_suggest.o myisam_suggest.c
   gcc -DDBUG_OFF -DDBUG_OFF -O -rdynamic -o myisam_suggest myisam_suggest.o libmyisam.a ../mysys/libmysys.a ../dbug/libdbug.a ../strings/libmystrings.a -lz -lpthread -lcrypt -lnsl -lm -lpthread
5. Copy the resulting myisam_suggest executable to /opt/myisam-suggest or somewhere where you want to have it. The Web server will call this tool later.

How to use

The tool is called like this:

You can find out the number of the full-text index by doing SHOW INDEX FROM table and counting the indices (beginning at 0).

For instance:

If the fulltext-index nr. 1 for this table contains words beginning with “tes”, these are listed. Sample output:

test
test
testcase
testing
testing
testing

Note that the tool must have read access to the MySQL table files, i.e. either these must be readable for everyone (not a good idea) or you setuid the tool to your MySQL user account (this is what I did):

This tool is in a very early state of development (more proof-of-concept) and I don’t work on it full-time. If you have suggestions or patches, please let me know via comment.

Possible applications

A possible application of this tool is to combine it with an AJAX AutoSuggest script to enable full-text index-driven search suggestions. See “3/3 Populate an AJAX AutoSuggest field on your Web site with myisam_suggest”.