I'm looking to set up an informational database which will be accessed by the general public on our website (there will be no internal users). The database will likely have about 10 million records with a total database size of 200-300 GB. All 10 million records will be very similar (the same data fields), so I anticipate that the entire database will be just 1 table. This is the main application that will be running on our server. Users will be querying the database by searching either a particular field (or multiple fields) or by doing a full text search.
My primary concern is that wait times (between the user doing a search query & then being able to view the results) will be long (I consider "long" to be more than 4 or 5 seconds). I was hoping someone could advise me on what dedicated server hardware to purchase (# of processors (physical and/or virtual), type of processors (Xeon, P4, 32-bit, 64-bit, HyperThreading, etc.), cache, RAM, etc.). We're on a very, very tight budget and don't want to OVER-deploy, but the speed of the database is also a major consideration.
Going along with that, can you also advise (in general terms) on how to optimally configure the enormous, 1-table database (and how to make it run optimally with the hardware configuration)?
Thank you very much!!!