I have a table which store some datas. This is my table structure.

I want to search from that table for example with this keyword Sertigo Seattle and it will return row number two as a result.

I have this query but doesn't work.

SELECT * FROM courses_data a WHERE CONCAT_WS(' ', a.Courses, a.Location) LIKE '%Sertigo Seattle%'

Maybe anyone knows how to make query to achieve my needs ?

Thank you.

CodePudding user response：

If you want to search against the course and location then use:

SELECT *
FROM courses_data
WHERE Course = 'Sertigo' AND Location = 'Seattle';

CodePudding user response：

Efficient searching is usually implemented by preparing the search string before running the actual search:

You split the search string "Sertigo Seattle" into two words: "Sertigo" and "Seattle". You trim those words (remove enclosing white space characters). You might also want to normalize the words, perhaps convert them to all lower case to implement a case insentive search.

Then you run a search for the discrete words:

SELECT *
  FROM courses_data
    WHERE 
      (Course = 'Sertigo' AND Location = 'Seattle')
    OR
      (Course = 'Seattle' AND Location = 'Sertigo');

Of course that query is created using a prepared statement and parameter binding, using the extracted and trimmed words as dynamic parameters.

This is is much more efficient than using wildcard based search with the LIKE operator. Because the database engine can make use of the indexes you (hopefully) created for that table. You can check that by using EXPLAIN feature MySQL offers.

Also it does make sense to measure performance: run different search approaches in a loop, say 1000 times, and take the required time. You will get a clear and meaningful example. Also monitoring CPU and memory usage in such a test is of interest.