How to Search Multiple Columns in MySQL

How to search multiple columns in MySQL?

You can use the AND or OR operators, depending on what you want the search to return.

SELECT title FROM pages WHERE my_col LIKE %$param1% AND another_col LIKE %$param2%;

Both clauses have to match for a record to be returned. Alternatively:

SELECT title FROM pages WHERE my_col LIKE %$param1% OR another_col LIKE %$param2%;

If either clause matches then the record will be returned.

For more about what you can do with MySQL SELECT queries, try the documentation.

MySQL search using multiple columns

As stated in the comments, performing a search with such "fluid" criteria will be really expensive on the DB.

Anyway, if this is the path you want to take, here's my attempt:

SELECT slug, title FROM catalog 
WHERE CONCAT(city, description) LIKE '$keyword'
OR CONCAT(description, city) LIKE '$keyword'

To make this work, you may wanna replace all spaces with % and maybe put leading and trailing % in the code.

I reckon the code which performs the query is PHP, so this could do it:

$keyword = "%" . str_replace(" ", "%", $keyword) . "%";

Like I said, this will be very very heavy on the DB, specially if the number of rows is important. Try to optimize the columns for full-text search and maybe make sure the search criteria can't be too short in the input form.

Hope this helps!

Fastest way to search multiple fields mysql

Am I correct is assuming that @search is replaced by something?

If you are searching for "words", then FULLTEXT works significantly faster. But, be aware of its limitations (stop words, min word length, etc).

That particular attempt, CONCAT(name,' ',surname) LIKE '@search%', would only check the beginning of name for '@search'; I doubt if that is what you wanted. What did you want?

Keep in mind that name LIKE '@search%' is a lot faster than name LIKE '%search%' because it can use INDEX(name), which I assume you have?

If you have both INDEX(name) and INDEX(surname), then this would be reasonably fast:

SELECT ...
WHERE name LIKE '@search%'
UNION DISTINCT
SELECT ...
WHERE surname LIKE '@search%';

This is because both indexes can be used. Otherwise, they are unlikely to both be used; instead there would be a "table scan". (The use of OR will not do as well if you have an old version of MySQL.)

However, if the user starts @search with '%' or '_', it will be sloooow because the leading wildcard will cause a table scan.

Meanwhile, if you don't check for various special characters, you are very subject to SQL Injection!

After clarification

Since the user is typing the names separately, you can much more easily test:

WHERE name    LIKE '@name%'
AND surname LIKE '@surname%'

Then have both of these (in case the user shortens either one):

INDEX(name, surname)
INDEX(surname, name)

Those are better than what you have, INDEX(name), INDEX(surname).

Since the data input seems to be a single field, use your client programming language for splitting it into @name and @surname. (It is possible, but messy, in SQL; see SUBSTRING_INDEX().)

Read about "composite indexes" to understand why these indexes are better, and why the original CONCAT is especially inefficient.

MySQL: Look for the same string in multiple columns

Simple workaround:

SELECT * 
FROM projects
WHERE
CONCAT(category,name,description,keywords,type) LIKE '%query%'
ORDER BY name ASC;

You can add separators between columns if needed:

SELECT * 
FROM projects
WHERE
CONCAT(category,"|",name,"|",description,"|",keywords,"|",type) LIKE '%query%'
ORDER BY name ASC;

You can also use a fulltext search (you need to create a fulltext index as described here: How do FULLTEXT INDEXES on multiple columns work?)

SELECT *, MATCH (category,name,description,keywords,type) AGAINST ('query') AS score FROM projects WHERE MATCH (category,name,description,keywords,type) AGAINST ('query');


Related Topics



Leave a reply



Submit