4 ms·
Ask HN: How to optimize search by multiple criteria?
I have to begin dealing with searching a huge site by multiple criteria, and I thought I'd ask HN for advice. Dating sites allow visitors to search by multiple criteria at once. Shopping sites have filters by multiple criteria people can activate. What is the actual indexing and querying strategy / algorithm here?
We currently MySQL to store data and relationships between tables, but do not use JOINs at all, because we may be sharding across many machines. Instead, each query is done first on the "index" rows and then the "related" rows are fetched in a separate query that is sharded/combined.
With that in mind, yes we could install Sphinx or Solr or some other solution that "just does it" for us. Or perhaps we could use a graph database for the search. My question is more about HOW the search is actually implemented efficiently!
From what I understand, an RDBMS simply tries to select the "best" index to use, and uses that one index, doing a full scan of all the results for the "sub" filtering. Or perhaps it uses other indexes for the sub filtering, if those indexes cover the secondary fields?
Maybe there is a way to precompute / warehouse the data across shards for faster queries without using more than O(log N) space?
Something like storing relationships between a discrete set of filter values and tuples, which in turn point to the various data rows on different shards?