Search functionality with pagination on a website

I'm developing a search functionality with pagination in my site to search for the product name and the brand name. I'm using this query to get the user's search request:

SELECT *
from products
WHERE name = 'optimum nutrition'
    OR brand = 'optimum nutrition' 
    OR name LIKE '%optimum%' 
    OR brand LIKE '%optimum%' 
    OR name LIKE '%nutrition%' 
    OR brand LIKE '%nutrition%'

I would like to display first the products that has the full 'optimum nutrition' in either the brand name and in the product name. How will I accomplish this?

Any suggestion would b开发者_运维问答e greatly appreciated.

Try:

SELECT *, 
CASE WHEN (name = 'optimum nutrition' OR brand = 'optimum nutrition') THEN 1 ELSE 0 END AS full_match,
CASE WHEN (name LIKE '%optimum%' OR brand LIKE '%optimum%' OR name LIKE '%nutrition%' OR brand LIKE '%nutrition%') THEN 1 ELSE 0 END AS half_match
FROM products 
WHERE (name = 'optimum nutrition' OR brand = 'optimum nutrition')
OR (name LIKE '%optimum%' OR brand LIKE '%optimum%' OR name LIKE '%nutrition%' OR brand LIKE '%nutrition%')
ORDER BY full_match, half_match

First I believe name = 'optimum nutrition' will also be returned by name LIKE '%optimum%'. this can work if you design a correct query. But LIKE is very slow and should be avoided, especially database is large. I just found this interesting presentation which used October's Stackoverflow data and did full text searching on it. I think this comparison sheet is interesting:

I will suggest you look into Zend Search Lucene, to put search functionality on your page.

I would suggest looking into MySQL full text search. This involves adding a FULLTEXT index on columns you want to search, and then using a query that will look something like this:

SELECT * 
FROM products
WHERE MATCH(name, brand) AGAINST ('optimum')