SQL query for flexible keyword matching in a database?

Data and Databases
1

I’m working on a website that needs to search a database based on user-entered keywords. Here’s my problem:

My table has a category column and five keyword columns (1-5). These hold things like ‘pizza’, ‘chicken’, or ‘bbq’.

I’m stuck on writing an SQL query that works when users enter keywords in any order. For example, both ‘Restaurant pizza’ and ‘pizza Restaurant’ should work.

This query doesn’t cut it:

SELECT * FROM menu_items
WHERE category LIKE '%Restaurant%' AND category LIKE '%pizza%'
   OR keyword1 LIKE '%Restaurant%' AND keyword1 LIKE '%pizza%'
   OR keyword2 LIKE '%Restaurant%' AND keyword2 LIKE '%pizza%'

Any ideas on how to make this work? Also, what’s the best way to index this for good performance?

Thanks for your help!

2

heyy, have u thought about using a different approach? maybe try concatenating all ur keyword columns into one big text field? then u could do something like:

SELECT * FROM menu_items WHERE CONCAT(category, ’ ', keyword1, ’ ', keyword2, ’ ', keyword3, ’ ', keyword4, ’ ', keyword5) LIKE ‘%Restaurant%’ AND LIKE ‘%pizza%’

might be easier to manage and search. what do u think?

3

Having worked on similar projects, I’d recommend exploring full-text search capabilities if your database supports it. It’s significantly more efficient than using LIKE with wildcards, especially for large datasets. For MySQL, you could create a FULLTEXT index on your keyword columns and use MATCH…AGAINST for searching. This approach handles multiple keywords well and offers better performance.

If full-text search isn’t an option, consider concatenating your keyword columns into a single field during insertion or updates. This allows for a simpler query structure and potentially better indexing options. Remember, the key is to balance flexibility with performance based on your specific use case and database size.

4

hey, have you considered using FULLTEXT indexing? it’s way better for keyword searches than LIKE. you could do something like:

CREATE FULLTEXT INDEX idx ON menu_items (category, keyword1, keyword2, keyword3, keyword4, keyword5);

then use MATCH…AGAINST for searching. it’ll handle multiple keywords and be faster too. just a thought!