Query takes over 30 minutes -- How can I speed up? (Explain and schema included)

Question

Goal: Display ethnicity data by district for each race and year as a percent of total.

Problem: Query takes over 30 minutes to run. I would like to get this down to under 10 seconds. Is there another strategy to solve this problem that I am not thinking of?

Query:

SELECT field_data_field_district_id.field_district_id_value as district_id, year, race, ROUND(SUM(count)/(
SELECT SUM(count)
FROM school_data_race_ethnicity_raw as school_data_race_ethnicity_raw_inner
INNER JOIN field_data_field_school_id as field_data_field_school_id_inner ON field_data_field_school_id_inner.field_school_id_value = school_data_race_ethnicity_raw_inner.school_id
INNER JOIN field_data_field_district as field_data_field_district_inner ON field_data_field_district_inner.entity_id = field_data_field_school_id_inner.entity_id
INNER JOIN field_data_field_district_id as field_data_field_district_id_inner ON field_data_field_district_inner.field_district_nid = field_data_field_district_id_inner.entity_id
WHERE field_data_field_district_id_inner.field_district_id_value = field_data_field_district_id.field_district_id_value and
school_data_race_ethnicity_raw_inner.year = school_data_race_ethnicity_raw.year
) * 100 ,2) as percent 
FROM school_data_race_ethnicity_raw
INNER JOIN field_data_field_school_id ON field_data_field_school_id.field_school_id_value = school_data_race_ethnicity_raw.school_id
INNER JOIN field_data_field_district ON field_data_field_district.entity_id = field_data_field_school_id.entity_id
INNER JOIN field_data_field_district_id ON field_data_field_district.field_district_nid = field_data_field_district_id.entity_id
GROUP BY district_id, year, race

Explain:

+----+--------------------+--------------------------------------+------+------------------------------+--------------------+---------+--------------------------------------------------------------------+-------+---------------------------------+
| id | select_type        | table                                | type | possible_keys                | key                | key_len | ref                                                                | rows  | Extra                           |
+----+--------------------+--------------------------------------+------+------------------------------+--------------------+---------+--------------------------------------------------------------------+-------+---------------------------------+
|  1 | PRIMARY            | field_data_field_district_id         | ALL  | entity_id                    | NULL               | NULL    | NULL                                                               |   685 | Using temporary; Using filesort |
|  1 | PRIMARY            | field_data_field_district            | ref  | entity_id,field_district_nid | field_district_nid | 5       | rocdocs_main_drupal_7.field_data_field_district_id.entity_id       |     3 | Using where; Using index        |
|  1 | PRIMARY            | field_data_field_school_id           | ref  | entity_id                    | entity_id          | 4       | rocdocs_main_drupal_7.field_data_field_district.entity_id          |     1 |                                 |
|  1 | PRIMARY            | school_data_race_ethnicity_raw       | ALL  | NULL                         | NULL               | NULL    | NULL                                                               | 97098 | Using where; Using join buffer  |
|  2 | DEPENDENT SUBQUERY | field_data_field_district_id_inner   | ALL  | entity_id                    | NULL               | NULL    | NULL                                                               |   685 | Using where                     |
|  2 | DEPENDENT SUBQUERY | field_data_field_district_inner      | ref  | entity_id,field_district_nid | field_district_nid | 5       | rocdocs_main_drupal_7.field_data_field_district_id_inner.entity_id |     3 | Using where; Using index        |
|  2 | DEPENDENT SUBQUERY | field_data_field_school_id_inner     | ref  | entity_id                    | entity_id          | 4       | rocdocs_main_drupal_7.field_data_field_district_inner.entity_id    |     1 |                                 |
|  2 | DEPENDENT SUBQUERY | school_data_race_ethnicity_raw_inner | ref  | year                         | year               | 4       | func                                                               | 32366 | Using where                     |
+----+--------------------+--------------------------------------+------+------------------------------+--------------------+---------+--------------------------------------------------------------------+-------+---------------------------------+

Tables:

school_data_race_ethnicity_raw - (Approx. 100,000 rows)

mysql> show create table school_data_race_ethnicity_raw;
+--------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                          | Create Table                                                                                                                                                                                                                                                                                                                                                                                                  |
+--------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| school_data_race_ethnicity_raw | CREATE TABLE `school_data_race_ethnicity_raw` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `school_id` varchar(255) NOT NULL,
  `year` int(11) NOT NULL,
  `race` varchar(255) NOT NULL,
  `count` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `school_id` (`school_id`,`year`),
  KEY `year` (`year`,`race`),
  KEY `school_id_2` (`school_id`)
) ENGINE=MyISAM AUTO_INCREMENT=97099 DEFAULT CHARSET=latin1 |
+--------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

field_data_field_school_id - Approx. 5000 rows

mysql> show create table field_data_field_school_id;
+----------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                      | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+----------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| field_data_field_school_id | CREATE TABLE `field_data_field_school_id` (
  `entity_type` varchar(128) NOT NULL DEFAULT '' COMMENT 'The entity type this data is attached to',
  `bundle` varchar(128) NOT NULL DEFAULT '' COMMENT 'The field instance bundle to which this row belongs, used when deleting a field instance',
  `deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'A boolean indicating whether this data item has been deleted',
  `entity_id` int(10) unsigned NOT NULL COMMENT 'The entity id this data is attached to',
  `revision_id` int(10) unsigned DEFAULT NULL COMMENT 'The entity revision id this data is attached to, or NULL if the entity type is not versioned',
  `language` varchar(32) NOT NULL DEFAULT '' COMMENT 'The language for this data item.',
  `delta` int(10) unsigned NOT NULL COMMENT 'The sequence number for this data item, used for multi-value fields',
  `field_school_id_value` varchar(255) DEFAULT NULL,
  `field_school_id_format` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`entity_type`,`entity_id`,`deleted`,`delta`,`language`),
  KEY `entity_type` (`entity_type`),
  KEY `bundle` (`bundle`),
  KEY `deleted` (`deleted`),
  KEY `entity_id` (`entity_id`),
  KEY `revision_id` (`revision_id`),
  KEY `language` (`language`),
  KEY `field_school_id_format` (`field_school_id_format`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Data storage for field 234 (field_school_id)' |
+----------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

field_data_field_district - Approx. 5000 rows

mysql> show create table field_data_field_district;
+---------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                     | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+---------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| field_data_field_district | CREATE TABLE `field_data_field_district` (
  `entity_type` varchar(128) NOT NULL DEFAULT '' COMMENT 'The entity type this data is attached to',
  `bundle` varchar(128) NOT NULL DEFAULT '' COMMENT 'The field instance bundle to which this row belongs, used when deleting a field instance',
  `deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'A boolean indicating whether this data item has been deleted',
  `entity_id` int(10) unsigned NOT NULL COMMENT 'The entity id this data is attached to',
  `revision_id` int(10) unsigned DEFAULT NULL COMMENT 'The entity revision id this data is attached to, or NULL if the entity type is not versioned',
  `language` varchar(32) NOT NULL DEFAULT '' COMMENT 'The language for this data item.',
  `delta` int(10) unsigned NOT NULL COMMENT 'The sequence number for this data item, used for multi-value fields',
  `field_district_nid` int(10) unsigned DEFAULT NULL,
  PRIMARY KEY (`entity_type`,`entity_id`,`deleted`,`delta`,`language`),
  KEY `entity_type` (`entity_type`),
  KEY `bundle` (`bundle`),
  KEY `deleted` (`deleted`),
  KEY `entity_id` (`entity_id`),
  KEY `revision_id` (`revision_id`),
  KEY `language` (`language`),
  KEY `field_district_nid` (`field_district_nid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Data storage for field 221 (field_district)' |
+---------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

field_data_field_district_id - Approx: 1000 rows

mysql> show create table field_data_field_district_id;
+------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table                        | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| field_data_field_district_id | CREATE TABLE `field_data_field_district_id` (
  `entity_type` varchar(128) NOT NULL DEFAULT '' COMMENT 'The entity type this data is attached to',
  `bundle` varchar(128) NOT NULL DEFAULT '' COMMENT 'The field instance bundle to which this row belongs, used when deleting a field instance',
  `deleted` tinyint(4) NOT NULL DEFAULT '0' COMMENT 'A boolean indicating whether this data item has been deleted',
  `entity_id` int(10) unsigned NOT NULL COMMENT 'The entity id this data is attached to',
  `revision_id` int(10) unsigned DEFAULT NULL COMMENT 'The entity revision id this data is attached to, or NULL if the entity type is not versioned',
  `language` varchar(32) NOT NULL DEFAULT '' COMMENT 'The language for this data item.',
  `delta` int(10) unsigned NOT NULL COMMENT 'The sequence number for this data item, used for multi-value fields',
  `field_district_id_value` varchar(255) DEFAULT NULL,
  `field_district_id_format` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`entity_type`,`entity_id`,`deleted`,`delta`,`language`),
  KEY `entity_type` (`entity_type`),
  KEY `bundle` (`bundle`),
  KEY `deleted` (`deleted`),
  KEY `entity_id` (`entity_id`),
  KEY `revision_id` (`revision_id`),
  KEY `language` (`language`),
  KEY `field_district_id_format` (`field_district_id_format`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Data storage for field 218 (field_district_id)' |
+------------------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

NOTE: The only table I can change is school_data_race_ethnicity_raw as the others are generated by drupal.

mysql> explain SELECT field_data_field_district_id_inner.field_district_id_value,
    ->             school_data_race_ethnicity_raw_inner.year,
    ->             SUM(count) as total
    ->         FROM school_data_race_ethnicity_raw as school_data_race_ethnicity_raw_inner
    ->         INNER JOIN field_data_field_school_id as field_data_field_school_id_inner 
    ->             ON field_data_field_school_id_inner.field_school_id_value 
    ->                 = school_data_race_ethnicity_raw_inner.school_id
    ->         INNER JOIN field_data_field_district as field_data_field_district_inner 
    ->             ON field_data_field_district_inner.entity_id 
    ->                 = field_data_field_school_id_inner.entity_id
    ->         INNER JOIN field_data_field_district_id as field_data_field_district_id_inner 
    ->             ON field_data_field_district_inner.field_district_nid 
    ->                 = field_data_field_district_id_inner.entity_id
    ->     GROUP BY field_district_id_value, year
    -> ;
+----+-------------+--------------------------------------+------+------------------------------+--------------------+---------+--------------------------------------------------------------------+-------+---------------------------------+
| id | select_type | table                                | type | possible_keys                | key                | key_len | ref                                                                | rows  | Extra                           |
+----+-------------+--------------------------------------+------+------------------------------+--------------------+---------+--------------------------------------------------------------------+-------+---------------------------------+
|  1 | SIMPLE      | field_data_field_district_id_inner   | ALL  | entity_id                    | NULL               | NULL    | NULL                                                               |   773 | Using temporary; Using filesort |
|  1 | SIMPLE      | field_data_field_district_inner      | ref  | entity_id,field_district_nid | field_district_nid | 5       | rocdocs_main_drupal_7.field_data_field_district_id_inner.entity_id |     3 | Using where; Using index        |
|  1 | SIMPLE      | field_data_field_school_id_inner     | ref  | entity_id                    | entity_id          | 4       | rocdocs_main_drupal_7.field_data_field_district_inner.entity_id    |     1 |                                 |
|  1 | SIMPLE      | school_data_race_ethnicity_raw_inner | ALL  | NULL                         | NULL               | NULL    | NULL                                                               | 97098 | Using where; Using join buffer  |
+----+-------------+--------------------------------------+------+------------------------------+--------------------+---------+--------------------------------------------------------------------+-------+---------------------------------+
4 rows in set (0.00 sec)

Answer 1

How about this:

SELECT field_data_field_district_id.field_district_id_value as district_id,
    x.year,
    race,
    ROUND(SUM(count)/(x.total) * 100 , 2) as percent 
FROM school_data_race_ethnicity_raw
INNER JOIN field_data_field_school_id 
    ON field_data_field_school_id.field_school_id_value 
        = school_data_race_ethnicity_raw.school_id
INNER JOIN field_data_field_district 
    ON field_data_field_district.entity_id 
        = field_data_field_school_id.entity_id
INNER JOIN field_data_field_district_id 
    ON field_data_field_district.field_district_nid 
        = field_data_field_district_id.entity_id
INNER JOIN (
    SELECT field_data_field_district_id_inner.field_district_id_value,
            school_data_race_ethnicity_raw_inner.year,
            SUM(count) as total
        FROM school_data_race_ethnicity_raw as school_data_race_ethnicity_raw_inner
        INNER JOIN field_data_field_school_id as field_data_field_school_id_inner 
            ON field_data_field_school_id_inner.field_school_id_value 
                = school_data_race_ethnicity_raw_inner.school_id
        INNER JOIN field_data_field_district as field_data_field_district_inner 
            ON field_data_field_district_inner.entity_id 
                = field_data_field_school_id_inner.entity_id
        INNER JOIN field_data_field_district_id as field_data_field_district_id_inner 
            ON field_data_field_district_inner.field_district_nid 
                = field_data_field_district_id_inner.entity_id
    GROUP BY field_district_id_value, year
) x ON x.field_district_id_value = field_data_field_district_id.field_district_id_value 
    AND x.year = school_data_race_ethnicity_raw.year
GROUP BY district_id, x.year, race

I basically moved your query that calculates the total count for each district/year out of the SELECT and into another JOIN.

Explain:

+----+-------------+--------------------------------------+------+------------------------------+--------------------+---------+--------------------------------------------------------------------+-------+---------------------------------+
| id | select_type | table                                | type | possible_keys                | key                | key_len | ref                                                                | rows  | Extra                           |
+----+-------------+--------------------------------------+------+------------------------------+--------------------+---------+--------------------------------------------------------------------+-------+---------------------------------+
|  1 | PRIMARY     | field_data_field_district_id         | ALL  | entity_id                    | NULL               | NULL    | NULL                                                               |   773 | Using temporary; Using filesort |
|  1 | PRIMARY     | field_data_field_district            | ref  | entity_id,field_district_nid | field_district_nid | 5       | rocdocs_main_drupal_7.field_data_field_district_id.entity_id       |     3 | Using where; Using index        |
|  1 | PRIMARY     | field_data_field_school_id           | ref  | entity_id                    | entity_id          | 4       | rocdocs_main_drupal_7.field_data_field_district.entity_id          |     1 |                                 |
|  1 | PRIMARY     | <derived2>                           | ALL  | NULL                         | NULL               | NULL    | NULL                                                               |  1902 | Using where; Using join buffer  |
|  1 | PRIMARY     | school_data_race_ethnicity_raw       | ref  | year                         | year               | 4       | x.year                                                             | 32366 | Using where                     |
|  2 | DERIVED     | field_data_field_district_id_inner   | ALL  | entity_id                    | NULL               | NULL    | NULL                                                               |   773 | Using temporary; Using filesort |
|  2 | DERIVED     | field_data_field_district_inner      | ref  | entity_id,field_district_nid | field_district_nid | 5       | rocdocs_main_drupal_7.field_data_field_district_id_inner.entity_id |     3 | Using where; Using index        |
|  2 | DERIVED     | field_data_field_school_id_inner     | ref  | entity_id                    | entity_id          | 4       | rocdocs_main_drupal_7.field_data_field_district_inner.entity_id    |     1 |                                 |
|  2 | DERIVED     | school_data_race_ethnicity_raw_inner | ALL  | NULL                         | NULL               | NULL    | NULL                                                               | 97098 | Using where; Using join buffer  |
+----+-------------+--------------------------------------+------+------------------------------+--------------------+---------+--------------------------------------------------------------------+-------+---------------------------------+
9 rows in set (4 min 0.59 sec)