Back
fulltext query on multiple attributes
I want to search a string that is equals or partially matched between multple attributes. For example I type the text "John" and I want to search that string in the name, surname, email fields. How can I do that, should I create a fulltext index with these 3 attributes (ASC or DESC or both)?
TL;DR
To search for a string in multiple attributes, you can create a new field named "search" and concatenate all the values you want to search, separated by spaces. Then, add a fulltext index only to this "search" field. This approach works better with MariaDB's full-text search.
To implement this solution:
1. Create a new field named "search" in your table.
2. Concatenate the values of the name, surname, and email fields and store them in the "search" field, separated by spaces.
3. Add a fulltext index only to the "search" field.
By using this approach,
The best approach would be to create another field named search within it add all the values you want to search separated by space.
Then add fulltext index only to that one.
The reason is that the fulltext index uses MariaDB full-text search and this way it works better.
