Most efficient search query

Question

I got a table of product which I would like to search for a product in there. At the momment, the query looks like this:

$query = "SELECT * FROM `products` WHERE `name` LIKE '%$qr_search%' LIMIT 0,$products_per_single_search";

As you can see, im using the %SOME_TEXT% method, which is nice, but not good enough.

If the user searches for pen he would get results such as:

pen
peny
pentra
somewordPENword

and so on. I'm not saying that it's irrelevant to have those results, but I would like to make some order in there, so the most relevant results will show first.

Is there any better mechanism for that?

For future code: You don't need to quote/backtick your column & table names. They only add visual clutter and are just one more way for you to make syntax errors. The only reason you need them is if you have a column name that is a reserved word, and using column names that are reserved words is a terrible idea, so that's two bad habits you can avoid at once. — Andy Lester, Sep 15 '13 at 14:20
Relevant: https://stackoverflow.com/questions/28385145/correct-way-to-use-like-var-with-prepared-statements-mysqli — Dharman, Dec 25 '19 at 17:39

score 2 · Accepted Answer · answered Sep 15 '13 at 06:42

2

There is no relevance engine built into MySql. But you can add this ORDER BY clause to add some rules to ordering:

ORDER BY CASE WHEN `name` LIKE '% $qr_search %' THEN 0
              WHEN `name` LIKE '% $qr_search' THEN 1
              WHEN `name` LIKE '$qr_search %' THEN 2
              ELSE 3
         END

answered Sep 15 '13 at 06:42

Hari Menon

31,521
13
78
107

Thanks a lot! I didn't know such a thing exists in SQL. works prefectly fine and as wanted ^_^ – kfirba Sep 15 '13 at 06:49

Most efficient search query

1 Answers1