MySQL DB search with partial exact math

Question

I have MySQL datatable of personal parameters (about 100 per person):

{"id":"1", "height":"182", "hair_color":"red", "eyes_color":"haze", "day_of_birth":"25"}

(JSON format is just for simplicity)

I must be able to implement partial search, to be able by, say, 5-1 parameters to get the result.

QUERY: ["height":182, "hair_color":"red", "day_of_birth":"25", "skin_color":"black"]

In the end I need list of matching row with NUMBER of matched parameters

LIKE: ["id":"1","matched":"2"]

And get, say "first 100" with biggest amount of matches (sure, ASAP :))

Answer 1

If you have to stick to an sql database, try something like the following:

select id, if (height=182,1,0)  
         + if(hair_color='red',1,0)
         +  if(day_of_birth=25,1,0)
         + if(skin_color='black',1,0) as matched
from person
order by matched desc
limit 100

For matching lots of entries with lots of parameters, however, there might be better choices, e.g. using a full text index like, for example, Apache Lucene / SOLR.