// First I have a table that has some objects, e.g. articles, with text I want to index, something very basic... in this case, I want to index the contents of the `content` field mysql> describe objects; +-----------+------------------+------+-----+-------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+------------------+------+-----+-------------------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | timestamp | timestamp | NO | | CURRENT_TIMESTAMP | | | content | longblob | YES | | NULL | | | user | int(10) unsigned | YES | MUL | NULL | | +-----------+------------------+------+-----+-------------------+----------------+ 6 rows in set (0.07 sec) // next a simple script is written, in any language, it will query the id and content fields of our // `objects` table, then split all words on whitespace in the `content` field. some basic filtering // can happen, like removing all words of less than 4 characters. i wont describe how to do this, // since this is pretty simple // now in our index table looks like this... mysql> describe indexes; +--------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | object | int(10) unsigned | NO | MUL | | | | string | varchar(255) | NO | MUL | | | +--------+------------------+------+-----+---------+----------------+ 3 rows in set (0.09 sec) // this assumes no word is longer than 255 chars, of course. `id` is just an auto_increment index // field, `object` refers to `objects`.`id` and `string` is a word that exists in // `objects`.`content`. // i created a couple entries in my objects table and manually input some of the words in // `objects`.`content` into my `indexes` table, here's the short list... mysql> select * from indexes; +----+--------+---------+ | id | object | string | +----+--------+---------+ | 1 | 2 | honest | | 2 | 2 | dares | | 3 | 2 | paine | | 4 | 2 | thomas | | 5 | 3 | propose | | 6 | 3 | toilet | | 7 | 3 | paper | +----+--------+---------+ 7 rows in set (0.01 sec) // now it would be pretty simple to do a single word search, for example if i seatrch for 'dares'.... mysql> select object,string from indexes where string = 'dares'; +--------+--------+ | object | string | +--------+--------+ | 2 | dares | +--------+--------+ 1 row in set (0.00 sec) // it tells me 'dares' is in `objects`.`id` = 2. but what if i want to do a multi-word search? this is the fun part. mysql> select object,string from indexes where string = 'dares' and string = 'toilet'; Empty set (0.00 sec) // this obviously would not work, since `string` for any entry would not match both, so how about // instead of using 'and', I use 'or' mysql> select object,string from indexes where string = 'dares' or string = 'toilet'; +--------+--------+ | object | string | +--------+--------+ | 2 | dares | | 3 | toilet | +--------+--------+ 2 rows in set (0.00 sec) // okay, that works, what about if the two of the query words exist in the same object, let's try // 'dares' and 'paine'.... mysql> select object,string from indexes where string = 'dares' or string = 'paine'; +--------+--------+ | object | string | +--------+--------+ | 2 | dares | | 2 | paine | +--------+--------+ 2 rows in set (0.01 sec) // now I have one object, 2, but two entries... I would normally have to write a script that could // figure out the duplicate object id's and all kinds of other stuff, but it can also be done in // your query.... mysql> select count(string) as count, truncate(((count(string) / 2) * 100),2) as perc, object, group_concat(string) as matches from indexes where string = 'paine' OR string = 'dares' group by object order by count desc; +-------+--------+--------+-------------+ | count | perc | object | matches | +-------+--------+--------+-------------+ | 2 | 100.00 | 2 | dares,paine | +-------+--------+--------+-------------+ 1 row in set (0.00 sec) // in this case... i've got `count` which is the number of matches for the object, percent which is // the percent of the words from the search string found in the object, the object id (in this case // '2') and finally the words that match... // let's try something different, just to see what would happen if more words were entered... mysql> select count(string) as count, truncate(((count(string) / 5) * 100),2) as perc, object, group_concat(string) as matches from indexes where string = 'honest' OR string = 'dares' OR string = 'thomas' OR string = 'toilet' OR string = 'paper' group by object order by count desc; +-------+-------+--------+---------------------+ | count | perc | object | matches | +-------+-------+--------+---------------------+ | 3 | 60.00 | 2 | dares,honest,thomas | | 2 | 40.00 | 3 | paper,toilet | +-------+-------+--------+---------------------+ 2 rows in set (0.00 sec) // in this case, the user entered into the search string the words: dares, honest, thomas, toilet, // and paper. so we search for each word from our indexes. since there were 5 words in our search // string, we use '5' to calculate `perc` [ count(string) / 5 ] // in addition, we can use SOUNDEX() to match based on the sound of the word... mysql> alter table indexes add column soundex varchar(64) after string; Query OK, 10 rows affected (0.09 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> update indexes set soundex = soundex(string); Query OK, 10 rows affected (0.00 sec) Rows matched: 10 Changed: 10 Warnings: 0 mysql> select * from indexes; +----+--------+---------+---------+ | id | object | string | soundex | +----+--------+---------+---------+ | 1 | 2 | honest | H523 | | 2 | 2 | dares | D620 | | 3 | 2 | paine | P500 | | 4 | 2 | thomas | T520 | | 5 | 3 | propose | P612 | | 6 | 3 | toilet | T430 | | 7 | 3 | paper | P600 | | 8 | 4 | toylet | T430 | | 9 | 4 | pane | P500 | | 10 | 4 | pay-per | P600 | +----+--------+---------+---------+ 10 rows in set (0.00 sec) // finally, our modified search string... mysql> select count(string) as count, truncate(((count(string) / 5) * 100),2) as perc, object, group_concat(string) as matches from indexes where soundex = soundex('honest') OR soundex = soundex('dares') OR soundex = soundex('thomas') OR soundex = soundex('toilet') OR soundex = soundex('paper') group by object order by count desc; +-------+-------+--------+---------------------+ | count | perc | object | matches | +-------+-------+--------+---------------------+ | 3 | 60.00 | 2 | honest,dares,thomas | | 2 | 40.00 | 3 | toilet,paper | | 2 | 40.00 | 4 | toylet,pay-per | +-------+-------+--------+---------------------+ 3 rows in set (0.00 sec)