title: large mysql (innodb) database - slow query performance, disappearing tables and long time to restore backups
body: <p>I've a database with 3 of the tables having rows in excess of 20 million each. I've used GUIDs as primary keys (unfortunately). Now our database is about 20GB and growing 5GB per month.</p>
  
  <p>It takes about 2 hrs to take full backup of the database, and 30hrs to restore on a box with 4GB RAM.</p>
  
  <p>We once have all the tables from database disappeared. other mysql databases in same server were alright except one - for which only data was disappeared leaving empty tables.</p>
  
  <p>A select query (among many slow queries) - which get max of a date column in one of 20m table takes about 5 mins to return result. This query used pretty frequently.</p>
  
  <p>What I'm looking answers for</p>
  
  <ol>
  <li>recommended db design changes</li>
  <li>ways to improved select query performance - max date column on 20m records</li>
  <li>other queries' performance</li>
  <li>how to go about handling future db growth</li>
  </ol>
  
  <p>Thanks all for your attention.</p>