Suppose I have this fields:
first name, last name, city, state, zip, lat, lng
I'm trying to find a way to store the data so I can do queries like:
Suppose I have millions of records, not sure if the best option is to put them all in one single table.
I could split them by state, but then I'll have to do a bunch of joins when searching.
Any ideas?
The more joins you have the slower things will get. If the data needs to be structured in a relational way for long term maintainability and extensibility than do so.
That MySQL table does not have to be where your fast search queries against.
You can MapReduce with Hadoop + Hive for high speed querying of lots of data, or, probably even easier to implement, you can build a Solr index that you query against.
In general, you will not achieve a very fast searchable large data set in MySQL or any relational database, MySQL is a great relational database store and will be fast for a while. But, depending on your searches and number of records, you will need the data to be grouped and aggregated differently and that is where something like a Solr or a NoSQL persistence/view of the data can get you the speeds you need.