Server Crash - Optimizing Mysql queries

Question

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.

None of my tables are very large nothing more than 10,000 rows. Here are some things my original host sent me:

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 

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:

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 

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!

Edit: Here is the explain on the query:

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

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!

Answer 1

There are a lot of things that could be affecting your query, using EXPLAIN should reveal the issues that have the most impact on performance.

But here are a few general guidelines:

• Ensure all the tables have an index on the fields used for joining the tables.
• Why do you use LEFT JOIN and then filter on concrete values on the WHERE clause? Using outer joins will increment the number of rows that would later be filtered and discarded by the conditions on the WHERE clause.
• Ensure that all the columns used for filtering are covered by an index. But, also, why do you filter by text values? Even if the city, state, and instrument fields are covered by an index, it takes more processing to compare strings than comparing integers.
• Why group by Person.id if then you order randomly? The grouping forces a sort operation that most probably requires a temporary table due to the table joins.

Now think also about this:

The way databases process joins in general is by combining all rows from each table and then discard the combinations that don't fulfill the join condition. That's why it's very important to have indexes on the join fields, because that way the combination uses the index keys and not a temporary table with all fields from both tables. That's also a reason not to use a LEFT JOIN and then later discard row combinations (in the WHERE clause) that could have been discarded earlier.

To get an idea of what this query is doing, and why it's affecting performance so badly calculate:

Total rows to filter = Number of rows in table Person
 * Number of rows in table TeacherDriveCity
 * Number of rows in table Instrument
 * Number of rows in table Teacher
 * Number of rows in table Upload
 * Number of rows in table TeacherBiography
 * Number of rows in table TeacherPhilosophy

I'm pretty sure that would be a very big number.

EXPLAIN interpretation

From the posted EXPLAIN results it's very bad:

• As mentioned, all the ALL values under join type mean that MySQL is doing a full table scan, that is it must read all the rows on table. This is very, very bad.
• There are no usable indexes as revealed by NULL under possible_keys for most of the join conditions. This is another reason for MySQL to do table scans, it must look through all the joined rows to filter the needed rows.

I would say that the worst part is filtering by city and state. Without joins a simple look up in the TeacherDriveCity by those two fields would require MySQL to look at potentially 7489 rows. Not to mention that both fields hold character strings.

Quick Fix

Add indexes on all columns used in JOIN and WHERE clauses. But consider not using outer (LEFT) joins on tables other that TeacherBiography and TeacherPhilosophy.