I need to get some comments and advice on best search practises for querying text or full text columns in multi million record tables (InnoDB storage engine, so I guess full text index is not the way to go. Server version: 5.1.44 and +).
I have 3 questions I appreciate if anyone helps
1 - What sort of index do you recommend?
2 - What is the best practise in regards to the query? For example I use something like this to search for phrases or words but I have heard there are better ways:3- In general what else should I consider? Any unexpected behaviour as it is a different data type?Code:SELECT description FROM `webmoosh_posts` WHERE `description` LIKE '%derived tables are%'