How can I improve my LIKE with JOIN lookup in mysql?

Question

I currently have a search that looks like this:

SELECT s.ID FROM Shoe s INNER JOIN Account a ON s.UserID = a.ID WHERE s.Publish='1' AND (s.Brand LIKE '%$Search%' OR s.Name LIKE '%$Search%' OR s.PrimaryColor LIKE '%$Search%' OR a.User LIKE '%$Search%') ORDER BY s.ID DESC LIMIT $offset, $rowsPerPage" 

This works great when I do a search like Blue or Nikes, but if I do a search like Blue Nicky, nothing returns. Should I use FULLTEXT ? How can I improve this? I want to be able to search all columns that may belong to a search variable.

Answer 1

So, after messing up and testing different things, I came up with the following:

 "FROM Shoe AS s LEFT JOIN Accounts AS a ON s.UserID = a.ID WHERE (MATCH (s.Brand, s.Name, s.PrimaryColor AGAINST('$Search' IN BOOLEAN MODE) OR MATCH (a.User) AGAINST('$Search' IN BOOLEAN MODE)) AND s.Publish='1' ORDER BY s.ID DESC" 

It looks like I fixed my problem, which I mentioned above, now I can perform a search such as "Blue Nike", and all elements related to blue and nike will be displayed. Not sure if this is the most efficient way to do this, but it works.

Answer 2

Instead of LIKE try using SOUNDEX or both.

Secondly, you can optimize the request to maintain capitalization, for example:

 (lower(s.Brand) LIKE '%" . strtolower($Search) . "%' OR lower(s.Name) LIKE '%" . strtolower($Search) . "%' OR lower(s.PrimaryColor) LIKE '%" . strtolower($Search) . "%' OR lower(a.User) LIKE '%" . strtolower($Search) . "%')