So I have a table in my database that has about a million race results. Out of the million, there are about 250,000 unique names (people have results for multiple races).
I'd like users to be able to quickly search for a name in the database. Obviously querying the results table isn't ideal.
So I was thinking of a nightly job that pulled all distinct names from the results table, and dumped it into a names table. But wondering if there might be a better alternative.
I was thinking redis may be an option, but, I'd like them to be able to do partial name searches John D would find John Doe for instance. I couldn't find if redis supported a query like that out of the box.
If you stick an index on the name column, that will likely get you there, depending on your particular performance requirements. Databases eat million row tables for breakfast. I have an app in production on a $10 digital ocean box that runs LIKE queries against an indexed text column on a MySQL database with over 300 million rows and it still comes back in 100ms or so.
If you aren't satisfied with the performance, you might look into something like Algolia or Elasticsearch or Solr. I can't give you meaningful links for those, but a quick google will net you approximately a billion results for them.
