order by, with like clause

MySQL & MariaDB Other MySQL® Questions
1

i am using this statment

select * from o_articles where catid=‘1’ and forid=‘2’ and ( article
like(‘%big house%’) OR article like(‘%big%’) OR article like(‘%house%’)
and status=‘1’ OR title like(‘%big house%’) ) order by ‘article’ asc
limit 0,10

there are two rows matching this , one having at its very begining the
word “big house” another have just the word “big”
i am trying to get them ordered by the NEAREST MATCHING comes first,
but it did not work, i change the Order by clause to all possible
values it did not work mad:

it always get orderd by the catid as it is the primary key,
the fields article is full text and title is varchar

if you can help i will be so thankful to you,
thanks

2

What you can use is a STRPOS function.

Are atid, forid and status stored as INTs in the table? You’re comparing them as strings, killing the performance and utalization of any indexes that could be used to speed up the execution of the query.

3

Ramma,

Your query is rather strange - you’re ordering by “article” which is constant why you would expect it to order by nearest match ?

In your case you can ether use boolean full text search and do order by “relevance” or explicitly count number of matched like statements, something like:

select (“aa” like “%a%”) + (“bbb” like “%b%”);

4

What you need is good’ol full text search. It’s a lot easier on the server