Question

我要最优化地履行我的SQL问题。

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;

我想像这样的价值观(le、 le、eo、 l)。

Answer 1

页: 1 缩略语指数为B-trees,因此,为了将其与指数加以缩小,搜索梯度必须与数值的开始相吻合。

页: 1 <代码>LIKE中的所有条件 WHERE Clause, 然后将每一条件列入。页: 1


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;

Answer 2

假设这些只是在[可能]更长的名称中加以说明,这是最快的,因为<代码> WHERE 条款比其他建议更快。

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;

。 Alas, the ORDER BY cannot be Superiord.

友情链接