How to write mysql query to search database?

Question

I'm own a wallpaper website and I'm trying to write a search feature that will search the database for the terms the user is searching for. I have 2 fields in the database I'm searching against TAGS and NAME

The current way I'm doing it is I take the search term divide it up into multiple words and then search the database using those terms. So if a user searches for "New York" my query will look like this

SELECT * FROM wallpapers 
WHERE tags LIKE '%New%' OR name LIKE '%new%' 
or tags LIKE '%York%' OR name LIKE '%York%'

The issue with that of course is that anything with the term new in it will be pulled up also like say "new car" etc. If I replace the query above with the following code then it's too vague and only like 2 wallpapers will show up

SELECT * FROM wallpapers 
WHERE tags LIKE '%New York%' OR name LIKE '%New York%'

Does anyone have a better way to write a search query?

Answer 1

Looks like you want to introduce the concept of relevance.

Try:

select * from (
SELECT 1 as relevance, * FROM wallpapers 
WHERE tags LIKE '%New York%' OR name LIKE '%New York%'
union
select 10 as relevance, * FROM wallpapers 
WHERE (tags LIKE '%New%' OR name LIKE '%new%') 
and (tags LIKE '%York%' OR name LIKE '%York%')
union
select 100 as relevance, * FROM wallpapers 
WHERE tags LIKE '%New%' OR name LIKE '%new%' 
union
select 100 as relevance, * FROM wallpapers 
WHERE tags LIKE '%York%' OR name LIKE '%York%'
)
order by relevance asc

By the way, this will perform very, very poorly if your database grows too large - you want to be formatting your columns consistently so they're all upper case (or all lower case), and you want to avoid wildcards in your where clauses if you possibly can.

Once this becomes a problem, look at full text searching.

Answer 2

Perhaps this is a really dumb question, but could be following possibly what you want?

SELECT * FROM wallpapers 
WHERE ( tags LIKE '%New%' OR name LIKE '%new%' )
and ( tags LIKE '%York%' OR name LIKE '%York%' )

This searches for wallpapers which must have both words but anywhere.

Warning Beware of SQL injection this way, when searching for "words" like new'york or new%york. Perhaps the most easy way is to treat all nonalpha/nonnumeric characters as spaces when splitting, such that new@york and similar becomes new and york.

Notes about searching:

Searching this way in databases is plain overkill (full table scan). As long as you only have a few wallpapers this is not a bigger problem. Nearly all current cheap hardware should be able to search through a million wallpapers within a second or so, as long as the database fits into memory.

However with bigger sites where the tags and name information exceeds available RAM, you certainly get a problem. Then it is time to try some other way to search. However what to do heavily depends on the expected use pattern, so to answer that more information is needed.