// 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 ]

// anyway, have fun!