Mysql Question posted in
The official MySQL documentation can be found here.

how i can optimize performance of this mysql query?

I want to optimize performance of this MySQL query

SELECT * FROM (
  (SELECT * FROM users USE INDEX(names) WHERE name LIKE '%leo%') UNION 
  (SELECT * FROM users USE INDEX(names) WHERE name LIKE '%le%') UNION
  (SELECT * FROM users USE INDEX(names) WHERE name LIKE '%eo%') UNION
  (SELECT * FROM users USE INDEX(names) WHERE name LIKE '%l%') UNION
  (SELECT * FROM users USE INDEX(names) WHERE name LIKE '%e%') UNION
  (SELECT * FROM users USE INDEX(names) WHERE name LIKE '%o%')
) as A LIMIT 5 OFFSET 5;

I want values sorted like this (leo, le, eo, l, e, o)

Tags:

2

Answers


  1. 0

    You can’t really optimize the LIKE condition if it begins with %. Indexes are B-trees, so the search string has to match the beginning of the value in order to narrow it down with an index.

    You can use OR the combine all the LIKE conditions in the WHERE clause, and then put each condition in the ORDER BY.

    SELECT *
FROM users
WHERE name LIKE '%leo%' OR name LIKE '%le%' OR name LIKE '%eo%' OR 
    name LIKE '%l%' OR name LIKE '%e%' OR name LIKE '%o%'
ORDER BY name LIKE '%leo%' DESC, name LIKE '%le%' DESC, name LIKE '%eo%' DESC, 
    name LIKE '%l%' DESC, name LIKE '%e%' DESC, name LIKE '%o%' DESC
LIMIT 5 OFFSET 5;

    If you can change from LIKE to = then you can simplify it to:

    SELECT *
FROM users
WHERE name in ('leo', 'le', 'eo', 'l', 'e', 'o')
ORDER BY FIELD(name, 'leo', 'le', 'eo', 'l', 'e', 'o')
LIMIT 5 OFFSET 5;
    Login or Signup to reply.
  2. 0

    Assuming these are just substrings in [possibly] longer names, this is the fastest because the WHERE clause is faster than the other suggestions.

    SELECT *
    FROM users
    WHERE name REGEXP "[leo]"
    ORDER BY name LIKE '%leo%' DESC,
             name LIKE '%le%' DESC,
             name LIKE '%eo%' DESC, 
             name LIKE '%l%' DESC,
             name LIKE '%e%' DESC,
             name LIKE '%o%' DESC;

    LIKE is faster than REGEXP for equivalent tests. Alas, the ORDER BY cannot be optimized.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search