Mysql – Which text-index I should create for xooops engine to achieve better search results

full-text-searchindexmyisamMySQL

In one of projects we use xoops engine to manage content. In mysql slow query log most of queries are following :

SELECT p.uid,f.forum_id, p.topic_id, p.poster_name, p.post_time, f.forum_name, p.post_id, p.subject
            FROM xps33_bb_posts p,
            xps33_bb_posts_text pt,
                xps33_bb_forums f WHERE p.post_id = pt.post_id AND p.approved = 1 AND p.forum_id = f.forum_id AND f.forum_id IN (1,4,61,7,9,17,20,45,35,44,38,39,43,53,54,55,56,57,58,60,14,29,40,26,18,41,33,24,32,59,25) AND ((p.subject LIKE '%rivi%' OR pt.post_text LIKE '%orvi%') AND (p.subject LIKE '%care%' OR pt.post_text LIKE '%gor%'))  ORDER BY p.post_time DESC LIMIT 0, 5;

I can't change them as It would involve changing the engine which is not an option atm. But I can help the engine to search faster. As I understood as the table uses MyIsam engine I can create text indicies which should make search faster, am I right?

So in general for which indicies I should create to avoid following queries run for long time?

+----+-------------+-------+--------+---------------------+---------+---------+--------------------+--------+-----------------------------+
| id | select_type | table | type   | possible_keys       | key     | key_len | ref                | rows   | Extra                       |
+----+-------------+-------+--------+---------------------+---------+---------+--------------------+--------+-----------------------------+
|  1 | SIMPLE      | p     | ALL    | PRIMARY,forumid_uid | NULL    | NULL    | NULL               | 144090 | Using where; Using filesort |
|  1 | SIMPLE      | f     | eq_ref | PRIMARY             | PRIMARY | 4       | diginew.p.forum_id |      1 | Using where                 |
|  1 | SIMPLE      | pt    | eq_ref | PRIMARY             | PRIMARY | 4       | diginew.p.post_id  |      1 | Using where                 |
+----+-------------+-------+--------+---------------------+---------+---------+--------------------+--------+-----------------------------+
3 rows in set (0.00 sec)

Best Answer

Your understanding isn't quite correct. No type of index on MySQL can be used to optimize LIKE '%string%' expressions.

LIKE 'string%' can use an ordinary b-tree index, but this only matches strings at the beginning of the column's value, which wouldn't help you.

You're correct that MyISAM (and InnoDB in MySQL 5.6) support full text indexes, but to query them, you have to use the a fulltext search query, which looks like this:

... WHERE MATCH (col1,col2,...) AGAINST (expr [search_modifier])

As things are now, if you do not have a multi-column index on (approved,post_time) in the xps33_bb_posts table, it seems like you should at least want that as a minimum, since the optimizer could then choose to read the only rows with approved = 1, ordered from newest to oldest, and the query would finish as soon as the first 5 rows were found (or whatever the LIMIT of the particular query required).

If you could take an example from your slow query log and post the output from EXPLAIN SELECT, you may get some additional helpful suggestions.

Related Question