We are building a system where we accumulate data from many of our internal services, process them and generate set of data called Jobs saved to database. Our client application running on client systems periodically requests for these Jobs and eligible Jobs for the requested client will be sent in the response. For each client request we need to search for qualified Jobs from Jobs table based on client request parameters. Example:Jobs:#1{    id: 1,   searchFields:{      key1:value1,      key2:value2,      key3:value3   },   job: <SOME COMMAND>}#2{    id: 2,   searchFields:{      key3:value3,      key4:value4,   },   job: <SOME COMMAND>}#3{    id: 3,   searchFields:{      key5:value5,      key6:value6,   },   job: <SOME COMMAND>}How search should work?We have set of attributes in client request, these attribute's values should match with attribute values in Job's search field searchField. If client request has attributes key1, key2 and key3 and job's searchField has key1 and key2 then this jobs is qualified only if the value of key1 and key2 of both client request and Job's searchField matches.Client Requests:# 1{      key1:value1,      key2:value2,      key3:value3}Job #1 is qualified. Job #2 is not qualified because request input does not have key4.# 2{      key1:value1,      key2:some_different_value,      key3:value3}No jobs are qualified because value of key2 doesn't match with any job# 3{      key1:value1,      key2:value2,      key3:value3,      key4:value4}Job #1 and #2 are qualified.# 3{      key2:value2,      key3:value3,      key4:value4,      key5:value5,      key6:value6,}Job #2 and #3 are qualified. Job #1 is not qualified because key1 does not exists in input dataWe have already built prototype for this system using MySQL database, but we feel MySQL is not well suited for these kind of systems. Jobs table is very huge and keeps growing (more than 1,00,000 records added each day) and searching Jobs based on multiple attributes just by using standard SQL queries (without indexed fields) is not efficient. Also, attributes in client request and Job's searchField are dynamic. We don't have fixed set of attributes to work on. New attributes can added or removed anytime, so if we are using SQL queries than handling dynamic attributes would be cumbersome.What we have tried?We created combinationKey and hashKey of all key-value attributes in searchField for each Job and saved it to database along with job.How combinationKey and hashkey are generated,Job:{    id: 1,   searchFields:{      key1:value1,      key2:value2,      key3:value3   },      job: <SOME COMMAND>}combinationKey=key1::key2::key3hashKey=sha256Of(key1=value1::key2=value2::key3=value3)And save Job as,{    id: 1,    combinationKey: key1::key2::key3,    hashKey:<Hash_Key>,   searchFields:{      key1:value1,      key2:value2,      key3:value3   },      job: <SOME COMMAND>}When we receive client request, we fetch unique combination keys from Jobs table (full table scan or cache) and generate hash for incoming request attributes,Unique combination keys from Jobs table: key1::key2::key3, key4::key5 ... etcClient Request: {      key1:value1,      key2:value2,      key3:value3,      key4:value4}Iterate through all combination keys and generate list of hashKeys for incoming request attributes and once hashKeys are generated, search for Jobs in Jobs table matching these hashKeys.This approach appears to be working fine in our prototype but I feel it is not efficient enough, because as Jobs data grow, there is a possibility  that unique combinationKey count could grow exponentially and for each client request, calculating hashkey for all combinationKeys would be computation intensive.What we need?Most efficient and easy way to search Jobs table basedon incoming client request attributes as per our requirements.Consistent and accurate search. We do not want to usesearch engines like Elasticsearch as we need accurate data and not for analytics.Best database suited for these kind of systems. Any companies (such as facebook, google) already using these kind of systems so we cananalyze their approach.Any help would be appreciated.
Efficient way to search data based on multiple attributes
design;architecture;database;database design;data modeling