Comparing myisam_suggest with a book’s index

Recently I had an idea how to illustrate how myisam_suggest works by a comparison with a normal printed book’s index:

“AutoSuggest” for a printed book

Image you have a thick book with hundreds of thousands of words in it. Now you want to know all words beginning with “fu” for some reason (= AutoSuggest).

Without the (full-text) index on the book’s last pages, you’d have to read the whole book, remember all words, sort them alphabetically, erase duplicates and then look at the words beginning with “fu”. A very time-intensive process.

However, if an index is available, you can just scroll to the index, scroll further to “f”, then “fu” and read the words there (ignoring the referenced page numbers). Takes almost no time.

AutoSuggest for MyISAM tables

The same process applies to MyISAM tables, too. Without full-text index, all tables would have to be scanned with something like WHERE description LIKE “% fu%” which would take unacceptable time and resources.

But with full-text index, there’s already a sorted list with all words where you can scroll to and then read all entries beginning with “fu” (in this case, ignoring the referenced records instead of the referenced pages). This is exactly what myisam_suggest does.

3/3 Populate an AJAX AutoSuggest field on your Web site with myisam_suggest

In “1/3 Implementing an AutoSuggest feature using MySQL fulltext indices” I described how you can use a MyISAM full-text index to extract search words for an AutoSuggest feature. Then, in “2/3 myisam_suggest: an AutoComplete tool for MySQL fulltext indices” I wrote how to install and use myisam_suggest to extract these search words in practice.

Here you will find how to use AJAX AutoSuggest together with my tool “myisam_suggest”.

For a demo, see www.gimpusers.com. Enter some characters (e.g. “brus”) into the search field on the top of the page.

Read more »

2/3 myisam_suggest: an AutoComplete tool for MySQL fulltext indices

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.
Read more »

1/3 Implementing an AutoSuggest feature using MySQL fulltext indices

The MySQL full-text index

Current MySQL versions provide a full-text index (FTI) which is generally used to index and search MyISAM (the default storage engine in MySQL) tables like this:

Internally, every indexed (text) column of a row is splitted into its words. Read more »