Slow search by index query LIKE% MYSQL

Question

i have table with 100 000 000 rows so large. Structure of table

id         int          INDEX(not primary not unique just index)
lang_index varchar(5)   INDEX
name       varchar(255) INDEX
enam       varchar(255) INDEX

Ok. i do query

1 Query

"SELECT name FROM table WHERE lang_index='en' AND name LIKE 'myname%'"

Speed is ok for this large table. around 0.02 sec.

i try 2 Query

"SELECT name FROM table WHERE lang_index='en' AND (name LIKE 'myname%' OR enam LIKE 'myname%')"

Very very slow around 230 sec!!!

then i try this 3 Query

"SELECT name FROM table WHERE lang_index='en' AND enam LIKE 'myname%'"

Speed is fantastic. around 0.02 sec.

Then i explode my 2nd query for two queries (1 and 3 query) its faster. around 0.04 sec but it not simply.

Why my query is slow? Two queries much faster than one. I need do this "SELECT name FROM table WHERE lang_index='en' AND (name LIKE 'myname%' OR enam LIKE 'myname%')" How i can make it faster?

Answer 1

The OR keyword drives MySQL's optimizer crazy.

You might try something like this.

SELECT name FROM table WHERE lang_index='en' AND name LIKE 'myname%'
UNION
SELECT name FROM table WHERE lang_index='en' AND enam LIKE 'myname%'

Or you might consider FULLTEXT searching. It requires MyISAM or a version of MySQL 5.6 or later.

EDIT* It's hard to know exactly what's going on with these optimization things. Can you try this? This will see whether the language selection is fouling you up.

 SELECT name 
   FROM table 
  WHERE (name LIKE 'myname%' OR enam LIKE 'myname%')

Can you try this?

SELECT name FROM table WHERE lang_index='en' AND name LIKE 'myname%'
UNION ALL
SELECT name FROM table WHERE lang_index='en' AND enam LIKE 'myname%'

It won't give a perfect result -- it will have duplicate name items -- but it will skip a DISTINCT deduplicating step in your query.

You might also try this.

SELECT name 
  FROM table
 WHERE lang_index='en'
   AND id IN (
    SELECT id from table 
     WHERE (name LIKE 'myname%' OR enam LIKE 'myname%'))