Fastest match query

[Henson4004]

I am using query:
SELECT id, project as name from projects WHERE project LIKE ‘%%%s%%’ ORDER BY project ASC LIMIT 10

Is there any better and optimized way as LIKE gets very slow for very large tables.

[ParkinT]

There is no value in using LIKE in this situation.
It appears you simply want ALL records (limited to 10).

SELECT id, project as name from projects ORDER BY project ASC LIMIT 10

And ASC can be omitted; I beleive it is the default.

[r937]

[quote=“ParkinT, post:2, topic:117536, full:true”]
There is no value in using LIKE in this situation.[/quote]really?

looks to me like project has to contain the letter “s”

[cpradio]

@r937

really?

looks to me like project has to contain the letter “s”

Maybe. If I had to guess, they are running it through sprintf or something similar where %s is a placeholder.

[Mittineague]

That’s what I find confusing

AFAIK
%%%s%%
would be the same as
%s%

https://dev.mysql.com/doc/refman/5.0/en/string-comparison-functions.html

Character	Description
%	Matches any number of characters, even zero characters
_	Matches exactly one character  

Wrong wildcard being used?

* BTW leading wildcards cause more look-ups

[Henson4004]

Infact %s is placeholder.

Even i use below it is slow and index doesn’t work on LIKE:
SELECT id, project as name from projects WHERE project LIKE ‘%Arab%’ ORDER BY project LIMIT 10

[r937]

an alternative would be a fulltext index

[ParkinT]

My mistake. I did not even see that ‘s’ (tree) among those ‘%’ (forest) !