I have close to 500K posts in my WordPress database. The site is not yet live, I have the data on my local computer. I was reading about the WOrdpress' https://wordpress.org/plugins/hyperdb/ plugin that may be useful. However, I have no clue how to get started with that. I asked a friend and he suggested maybe I split the databases and use a plugin like that to "talk-to" the different database. For example, he suggested I use a temporary database/table to pull posts from recently added posts. Temporary database to pull posts based on the users current location, etc. What is your suggestion on running a large database?
Ditto Otto -
Q: "What is your suggestion on running a large database?"
A: Don't do it on shared hosting.
Shared hosts have CPU limits, so any queries run on a database that large will be slow or time out. Shared hosts also have database size limits, so your database with 500K posts may be too large to upload and run.
You need at very least a VPS, and one that gives you root access so you can configure MySQL for loads and large databases with tools like http://mysqltuner.com/