Comparing myisam_suggest with a book’s index
Tags: AutoComplete, AutoSuggest, full-text index, MyISAM
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.