What's is the best way to create indexes for the query below

Question

I have this query:

select * from `metro_stations`
where `is_active` = 1
  and (`title` like '%search%' or `title_en` like '%search%')

How to create effective indexes if is_active is TINYINT field and titles are VARCHAR(255) ?

And what about this query:

select * from `metro_stations`
where `is_active` = 1
 and (`title` like '%search%' or
      `title_en` like '%search%' or
      `description` like '%search%')

if description field is text?

FYI https://en.wikipedia.org/wiki/Sargable. `LIKE` is SARGable but only with `%` at the end `search%` see some sample here http://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable — Juan Carlos Oropeza, Nov 20 '15 at 14:47

score 0 · Accepted Answer · answered Nov 20 '15 at 14:58

0

use full text index for each column. If use in query "or" use seperately fts index, if use "and" mix fts index (in one index use several column) Full Text Index

answered Nov 20 '15 at 14:58

Ramin Darvishov

1,043
1
15
30

i added two different full text indexes for title and title_en fields, but query: EXPLAIN EXTENDED select `id` from `metro_stations` where `is_active` = 1 and (`title` like "%мото%" or `title_en` like "%мото%") doesn't seem to use indexes. How is it possible ? – Oles Kashchenko Nov 20 '15 at 15:04
select id from metro_stations WHERE match(title) against('мото' IN BOOLEAN MODE) – Ramin Darvishov Nov 20 '15 at 15:09
OK, its my mistake. Thank You! What about performance ? Is this decision better than using LIKE '%search%' without any indexes ? – Oles Kashchenko Nov 20 '15 at 15:12
Performance analysis of MySQL's FULLTEXT indexes and LIKE queries for full text search http://makandracards.com/makandra/12813-performance-analysis-of-mysql-s-fulltext-indexes-and-like-queries-for-full-text-search – Ramin Darvishov Nov 20 '15 at 15:15
Read this but don't understand about InnoDB and MyISAM. My table has InnoDB engine. Can I use indexes in this situation ? – Oles Kashchenko Nov 20 '15 at 15:18
Full-text indexes can be used only with MyISAM tables. (In MySQL 5.6 and up, they can also be used with InnoDB tables.) Check your mysql version – Ramin Darvishov Nov 20 '15 at 15:20
Don't forget to mark as Answered (with green check mark) that which solves your problem or answers it otherwise (like the Q you had from 2013) – Drew Nov 20 '15 at 15:24
Marked as Answered :) – Oles Kashchenko Nov 20 '15 at 15:26

score 0 · Answer 2 · answered Nov 28 '15 at 00:32

0

FULLTEXT(title, title_en)

WHERE is_active = 1
  AND MATCH(title, title_en) AGAINST ("+search" IN BOOLEAN MODE)

(This should work for either InnoDB (5.6+) or MyISAM.)

Keep in mind the limitations of "word length" and "stop words" in FULLTEXT.

answered Nov 28 '15 at 00:32

Rick James

135,179
13
127
222

What's is the best way to create indexes for the query below

2 Answers2