title: Server Crash - Optimizing Mysql queries
body: <p>I've been on shared hosting for a long time and have been running the same queries for months without a problem.  All of the sudden I get an email from my host saying they put a block on my database because "The database was found to be consuming an inordinate amount of processor time".  What I don't understand is why now?  So anyways I thought maybe I need to go VPS.  Trying to get my site up on a VPS and immediately it the whole site has an out of memory limit error and has crashed.  For a while the connection was timing out while going to the site at all.  </p>
  
  <p>None of my tables are very large nothing more than 10,000 rows.  Here are some things my original host sent me:</p>
  
  <pre><code>DB_USER: my_user -- TOTAL_CONNECTIONS: 37 -- CONNECTED_TIME: 4906 --
  CPU_TIME: 1920 -- TABLE_ROW_READS: 79999077 -- SELECT_COMMANDS: 214 --
  UPDATE_COMMANDS: -- BUSY_TIME: 4879 -- BYTES_SENT: 252225 --
  BYTES_RECEIVED: 105418 -- WAIT_TIME (IO): 2959 
  </code></pre>
  
  <p>Table row reads 79,999,077??  Seriously how does that work.  My tables are all small.  I don't get a lot of traffic.  Maybe 100-150 people a day.  But that doesn't seem to matter because the site completely died right when I put it up on the new server.  Here is a query that was in the email saying my database is down.  Is it really only this one query that's killing me?  It's been up for literally months without a problem:</p>
  
  <pre><code>SELECT `Person`.`first_name`, `Person`.`last_name`,`Person`.`id`,`Upload`.`path`,`TeacherBiography`.`final_biography`, `TeacherPhilosophy`.`final_philosophy`
  FROM `my_database`.`people` AS `Person`
  LEFT JOIN `my_database`.`teacher_drive_cities` AS `TeacherDriveCity`
      ON (`TeacherDriveCity`.`person_id` = `Person`.`id`)
  LEFT JOIN `my_database`.`instruments_people` AS `InstrumentsPerson`
      ON (`InstrumentsPerson`.`person_id` = `Person`.`id`)
  LEFT JOIN `my_database`.`instruments` AS `Instrument`
      ON (`Instrument`.`id` = `InstrumentsPerson`.`instrument_id`)
  LEFT JOIN `my_database`.`teachers` AS `Teacher`
      ON (`Teacher`.`person_id` = `Person`.`id`)
  LEFT JOIN `my_database`.`uploads` AS `Upload`
      ON (`Upload`.`person_id` = `Person`.`id`)
  LEFT JOIN `my_database`.`teacher_biographies` AS `TeacherBiography`
      ON (`TeacherBiography`.`person_id` = `Person`.`id`)
  LEFT JOIN `my_database`.`teacher_philosophies` AS `TeacherPhilosophy`
      ON (`TeacherPhilosophy`.`person_id` = `Person`.`id`)
  WHERE `TeacherDriveCit! y`.`city` = 'Dana Point'
      AND `TeacherDriveCity`.`state` = 'ca'
      AND `Instrument`.`instrument` = 'saxophone'
      AND `Teacher`.`status` = 6
      AND `Upload`.`description` = 'profile_picture'
      AND `TeacherBiography`.`final_biography` IS NOT NULL
      AND `TeacherPhilosophy`.`final_philosophy` IS NOT NULL
  GROUP BY `Person`.`id`
  ORDER BY RAND() ASC
  LIMIT 3 
  </code></pre>
  
  <p>I've heard order by rand() is slow, but I didn't think it would crash the entire server in one call.  Am I missing something here?  Any help would be appreciated!  My sites been down for 24 hours and I live off the income from it.  Thanks! </p>
  
  <p>Edit: Here is the explain on the query:</p>
  
  <pre><code>1   SIMPLE  TeacherBiography    ALL NULL    NULL    NULL    NULL    41    Using where; Using temporary; Using filesort
  1   SIMPLE  TeacherPhilosophy   ALL NULL    NULL    NULL    NULL    41  Using where; Using join buffer
  1   SIMPLE  Upload              ALL NULL    NULL    NULL    NULL    166 Using where; Using join buffer
  1   SIMPLE  Teacher             ALL NULL    NULL    NULL    NULL    381 Using where; Using join buffer
  1   SIMPLE  InstrumentsPerson   ALL NULL    NULL    NULL    NULL    647 Using join buffer
  1   SIMPLE  Instrument  eq_ref  PRIMARY PRIMARY 4   yml_yml.InstrumentsPerson.instrument_id 1   Using where
  1   SIMPLE  Person  eq_ref  PRIMARY PRIMARY 4   yml_yml.TeacherPhilosophy.person_id     1   Using where
  1   SIMPLE  TeacherDriveCity    ALL NULL    NULL    NULL    NULL    7489    Using where; Using join buffer
  </code></pre>
  
  <p>I know it's not formatted so you can read it easy...  I don't know how to make it a table and what not here.  Sorry!</p>