Working on a project where schema is something like this:
id , key, value
The key and value columns are varchar, and the table is InnoDB.
A user can search on the basis of key value pairs ... Whats the best way to query in MySQL ? the options I can think of is:
For each key => value form a query and perform an inner join to get id matching all criterias.
Or in the background, populate a MyISAM table id, info with Full Text index on info and a single query using like '%key:value%key2:value2%'. The benefit of this will be later on if the website is popular and the table has a hundred thousand rows, I can easily port the code to Lucene but for now MySQL.
The pattern you're talking about is called relational division.
Option #1 (the self-join) is a much faster solution if you have the right indexes.
I compared the performance for a couple of solutions to relational division in my presentation SQL Query Patterns, Optimized. The self-join solution worked in 0.005 seconds even against a table with millions of rows.
Option #2 with fulltext isn't correct anyway as you've written it, because you wouldn't use LIKE with fulltext search. You'd use MATCH(info) AGAINST('...' IN BOOLEAN MODE). I'm not sure you can use patterns in key:value format anyway. MySQL FTS prefers to match words.