Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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)
share|improve this question

1 Answer 1

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.

share|improve this answer
    
I'v added explain for select in original post. –  Jevgeni Smirnov Mar 1 '13 at 9:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.