I had a site that I had a lot of trouble with the database. It was a 32 bit MySQL database. I think it was MySQL 5. It was on a 2.2 GHz dual core pentium with 2 GB ram which was running windows XP. (I forget what program we used to broadcast the site.) I owned the server and I was the only one on it.
That database made my computer chug even though I had primary keys and indexes. My biggest table had over a million rows and, like, 50 colums (it had the genes for each dog in my dog game). I had up to 100 people on my site at a time.
My database for the forums had two hundred thousand and some odd rows. There were also other tables for the dogs, all of which had a million+ rows, though most had no more than, like, 15 columns.
My game was sophisticated and lots, and lots, and lots of pages on the site accessed those big tables. In several places (namely the search where you could search for users, etc.) it used joins which, in my experience, are slower than queries that just look at one table.
Was this just too much for my little server to handle? Or could I had made things more efficient by putting certain stuff in its own database? Somebody once suggested that I put the dead dogs, and all their rows in the dog tables, in another database since dead dogs aren't accessed as much as live ones.
