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