I am trying to find the most efficient method to do a text search on MySQL and return partial results. My database consists of over 1M rows. It has a column with a string, which can be empty, containing tags separated by |.
Valid examples are:
yellow, green|yellow|blue|brown, , green|blue, etc.
The tags list will grow over time, so it is not fixed.
I want to do a search on one, or more, tags. For example: red, red|yellow, red|white|green, etc.
Is it better to do a string match on each row, or create a table with all the tags and the index of rows that have these tags?
Also, I want to do partial matches. For example, if I query for red|green|blue, I would like to get the following matches, in order:
- Anything that has red AND green AND blue
- Anything that has (red AND green) OR (red AND blue) OR (green AND blue)
- Anything that has red OR green OR blue
If I create a second table with tags pointing to indices in the original table, I could do intersections between the Ids of rows with red and the Ids of rows with green for example, but considering the number of rows, this could be very slow.
Any help about where to look at, existing solutions, etc would be awesome!
