Hi,
Below is the table I have
mysql> describe eventlog;
+-------------+------------------+------+-----+
| Field | Type | Null | Key |
+-------------+------------------+------+-----+
| id | int(10) unsigned | | PRI |
| timestamp | int(10) unsigned | | MUL |
| type | tinyint(4) | | MUL |
| source | char(15) | | MUL |
| description | char(100) | | MUL |
+-------------+------------------+------+-----+
currently, each column are individual indexes and were defined by:
mysql> alter table eventlog index (timestamp);
mysql> alter table eventlog index (type);
mysql> alter table eventlog index (source);
mysql> alter table eventlog index (description);
I am having performance problem with search. I would like to search
the table given a word or words that are found in 'description'.
Currently, I am doing it this way:
SELECT * FROM eventlog WHERE
description LIKE '%word1%' AND
description LIKE '%word2%' AND
description LIKE '%word3%'
ORDER by id DESC LIMIT 20;
Reading the documentations, it was mentioned that doing a
"LIKE '%word%'" search will not use indexes.
I ask, how do I perform search effectively on such a table?
DO you suggest that I create an "index table" wherein each
word in the eventlog description now forms a single row in
this index table that cross references the eventlog table?
mysql> describe indextable;
+-------------+------------------+------+-----+
| Field | Type | Null | Key |
+-------------+------------------+------+-----+
| id | int(10) unsigned | | MUL |
| keyword | char(20) | | MUL |
+-------------+------------------+------+-----+
SELECT e.* FROM eventlog e, indextable i WHERE
(i.keyword = 'word1' OR
i.keyword = 'word2' OR
i.keyword = 'word3')
AND e.id = i.id
ORDER by i.id DESC LIMIT 20;
I dont like doing the above workaround as it would take another
messy table, any suggestions?
Jaime
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
