MySQL choose wrong index

Question

I have tables with columns

blog_detail - id INT PK, blog_id INT FK, language INT, title VARCHAR(255).

blog - id INT PK, author_id INT FK, created DATETIME

I have indexes in table blog_detail

blog_id_language - blog_id, language

blog_id - blog_id

blog_id_title - blog_id, title

Query is:

SELECT  *
    FROM  `blog`
    LEFT JOIN  `blog_detail`  ON `blog`.`id` = `blog_detail`.`blog_id`
    WHERE  (`blog`.`is_active` = 1)
      AND  ((      `blog`.`id`           LIKE "%fulltextsearch%")
              OR  (`blog_detail`.`title` LIKE "%fulltextsearch%")
           )

MySQL choose index blog_id_language that uses column that is not used at all. Why MySQL does not choose only blog_id without useless column language or blog_id_title that is use both used columns?

You only showed us a (partial) definition for a single table, yet your query has at least one join, implying that two or more tables are involved in the query. Please show table definitions for all tables, and also show us the full query. — Tim Biegeleisen, Dec 20 '18 at 15:11
You have one index or 3 indexes? Please put the whole query would be easy to find the solution — Ntwobike, Dec 20 '18 at 15:12
Please include the **whole query**, and also the execution plan. — The Impaler, Dec 20 '18 at 15:13
`I have indexes - blog_id_language, blog_id, blog_id_title` - A lot of people think that adding single-column indexes to all columns will help with the query performance. This is simply not true. — The Impaler, Dec 20 '18 at 15:14
@TheImpaler these are 3 indexes, I edited the question to make it more obvious — tttpapi, Dec 20 '18 at 15:36
Are you sure those indexes are correct? The table `blog_detail` does not have the columns `language` or `title`. Maybe these indexes are on the table `blog`? Your question looks interesting; please check it's correct. — The Impaler, Dec 20 '18 at 15:45
Please provide `SHOW CREATE TABLE` for both tables and `EXPLAIN SELECT ...` — Rick James, Dec 20 '18 at 23:13

score 1 · Accepted Answer · answered Dec 20 '18 at 16:20

You are creating what I assume are normal column indexes, which index the exact value of the column - these cannot be used with pattern-match style queries (e.g. "LIKE '%text%'"). However they will work on beginning-matching (e.g. "LIKE 'text%'").

You either need to change your blog.id and blog_detail.title indexes to be of type FULLTEXT and use proper FTS (Full Text Search), or break down the scenarios that you have into multiple beginning-matching criteria.

Alternatively, you can use something like lucene to do the indexing and searching for you.

Check out this question for more in-depth answers:

How to speed up SELECT .. LIKE queries in MySQL on multiple columns?

MySQL choose wrong index

1 Answers1