I'm executing the following query on a Cloud SQL instance (type D1 mysql 5.6.16) where it takes 11 seconds to complete. On the exact same database with the same indexes and content running on mysql (v5.5.35) on my shared hosting (which is nothing special in terms of specs) the query takes just 0.068 seconds.
Query:
SELECT casenotes.caseworkerID,COUNT(*)
FROM emails LEFT JOIN (casenotes) ON (emails.ID=casenotes.subtypeID)
WHERE emails.type='sent' AND casenotes.type='email' AND
emails.datetime > '2014-05-01'
GROUP BY casenotes.caseworkerID;
This is the explain result on Cloud SQL server
id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE casenotes ref type,subtypeID,caseworkerID type 22 const 6447 Using index condition; Using where; Using temporary; Using filesort 1 SIMPLE emails eq_ref PRIMARY,ID,type,datetime PRIMARY 8 casework_test.casenotes.subtypeID 1 Using where
This is the explain result on my shared hosting
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE casenotes ref type,subtypeID type 22 const 6451 Using where; Using temporary; Using filesort 1 SIMPLE emails eq_ref PRIMARY,type,datetime PRIMARY 8 zahawizs_caseworker.casenotes.subtypeID 1 Using where
Does anyone have any suggestions for how I can tweak either my select statement or the Cloud SQL server instance to return this faster.
