title: MySQL's query is stuck and cpu is 100% on live but not on local
body: <p>I have a problem with certain pages on Django Admin that make MySQL's query stuck (cpu is 100%).</p>
  
  <p>A couple of weeks ago, I had to convert all tables from MyISAM to InnoDB. This operation worked fine.</p>
  
  <p>But on certain pages, the request was frozen. After some investigations, I found out that it was MySQL's query.</p>
  
  <p>I noticed that foreign keys were missing. So I added the missing foreign keys and the query worked. It works on my machine (localhost) but not on live. Every time I load the page on live, I have to restart the MySQL server.</p>
  
  <p>I ran the query via MySQL workbench:</p>
  
  <ul>
  <li>on local: OK</li>
  <li>on live: stuck</li>
  </ul>
  
  <p>Live env: AWS (EC2 with RDS) with <code>Django 1.10.5</code> and <code>MySQL 5.6.x</code> 
  Local env: <code>Django 1.10.5</code> and <code>MySQL 5.7.x</code></p>
  
  <p>I am stuck on finding a solution and I don't know what to do now or to look for.</p>
  
  <p>Please advise.</p>
  
  <p><strong>* EDIT 31-01-2017 *</strong></p>
  
  <p>Query being executed:</p>
  
  <pre><code>SELECT `clients_campaignday`.*, `clients_campaign`.*,  `clients_client`.*
   FROM `clients_campaignday` 
   INNER JOIN `clients_campaign` ON (`clients_campaignday`.`campaign_id` = `clients_campaign`.`id`) 
   INNER JOIN `clients_client` ON (`clients_campaign`.`client_id` = `clients_client`.`id`) 
  ORDER BY `clients_campaignday`.`day` DESC, `clients_campaignday`.`id` DESC LIMIT 50
  </code></pre>
  
  <p>Explain query on <strong>local</strong>:</p>
  
  <pre><code>+----+-------------+---------------------+------------+--------+------------------------------------------------------------------+---------+---------+-------------------------------------------------+-------+----------+----------------+
  | id | select_type | table               | partitions | type   | possible_keys                                                    | key     | key_len | ref                                             | rows  | filtered | Extra          |
  +----+-------------+---------------------+------------+--------+------------------------------------------------------------------+---------+---------+-------------------------------------------------+-------+----------+----------------+
  |  1 | SIMPLE      | clients_campaignday | NULL       | ALL    | clients_campaignday_6cd0afe2,idx_clients_campaignday_campaign_id | NULL    | NULL    | NULL                                            | 51839 |   100.00 | Using filesort |
  |  1 | SIMPLE      | clients_campaign    | NULL       | eq_ref | PRIMARY,clients_campaign_4fea5d6a                                | PRIMARY | 4       | tool_2017_01_30.clients_campaignday.campaign_id |     1 |   100.00 | NULL           |
  |  1 | SIMPLE      | clients_client      | NULL       | eq_ref | PRIMARY                                                          | PRIMARY | 4       | tool_2017_01_30.clients_campaign.client_id      |     1 |   100.00 | NULL           |
  +----+-------------+---------------------+------------+--------+------------------------------------------------------------------+---------+---------+-------------------------------------------------+-------+----------+----------------+
  </code></pre>
  
  <p>Explain query on <strong>live</strong>:</p>
  
  <pre><code>+----+-------------+---------------------+------------+--------+----------------------------------------------------------------------------+-----------------------------------------------+---------+-----------------------------------------+------+----------+---------------------------------+
  | id | select_type | table               | partitions | type   | possible_keys                                                              | key                                           | key_len | ref                                     | rows | filtered | Extra                           |
  +----+-------------+---------------------+------------+--------+----------------------------------------------------------------------------+-----------------------------------------------+---------+-----------------------------------------+------+----------+---------------------------------+
  |  1 | SIMPLE      | clients_campaign    | NULL       | ALL    | PRIMARY,clients_campaign_4fea5d6a                                          | NULL                                          | NULL    | NULL                                    |  145 |   100.00 | Using temporary; Using filesort |
  |  1 | SIMPLE      | clients_client      | NULL       | eq_ref | PRIMARY                                                                    | PRIMARY                                       | 4       | tool_v3_prod.clients_campaign.client_id |    1 |   100.00 | NULL                            |
  |  1 | SIMPLE      | clients_campaignday | NULL       | ref    | clients_campaignday_campaign_id_283ca030_uniq,clients_campaignday_6cd0afe2 | clients_campaignday_campaign_id_283ca030_uniq | 4       | tool_v3_prod.clients_campaign.id        |  201 |   100.00 | NULL                            |
  +----+-------------+---------------------+------------+--------+----------------------------------------------------------------------------+-----------------------------------------------+---------+-----------------------------------------+------+----------+---------------------------------+
  </code></pre>
  
  <p>SHOW CREATE TABLE on <strong>local</strong>:</p>
  
  <pre><code>CREATE TABLE `clients_campaignday` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `campaign_id` int(11) NOT NULL,
    `day` date NOT NULL,
    `cache_power` int(11) DEFAULT NULL,
    `nb_articles` int(11) DEFAULT NULL,
    `add_datetime` datetime NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `clients_campaignday_campaign_id_283ca030_uniq` (`campaign_id`,`day`),
    KEY `clients_campaignday_6cd0afe2` (`campaign_id`),
    CONSTRAINT `clients_campaignday_ibfk_1` FOREIGN KEY (`campaign_id`) REFERENCES `clients_campaign` (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=80960 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
  </code></pre>
  
  <p>SHOW CREATE TABLE on <strong>live</strong>:</p>
  
  <pre><code>CREATE TABLE `clients_campaignday` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `campaign_id` int(11) NOT NULL,
    `day` date NOT NULL,
    `cache_power` int(11) DEFAULT NULL,
    `nb_articles` int(11) DEFAULT NULL,
    `add_datetime` datetime NOT NULL,
    PRIMARY KEY (`id`),
    KEY `clients_campaignday_6cd0afe2` (`campaign_id`),
    KEY `idx_clients_campaignday_campaign_id` (`campaign_id`),
    CONSTRAINT `clients_campaignday_campaign_id_6dac75d8_fk_clients_campaign_id` FOREIGN KEY (`campaign_id`) REFERENCES `clients_campaign` (`id`)
  ) ENGINE=InnoDB AUTO_INCREMENT=80960 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
  </code></pre>
  
  <p><strong>* EDIT 02-02-2017 *</strong></p>
  
  <p>I found some leads on my local after investigating the EXPLAIN results.</p>
  
  <p>I applied a SQL query that add foreign keys and <strong>optimize</strong> the database:</p>
  
  <pre><code>-- Table : clients_campaign
  ALTER TABLE clients_campaign ADD CONSTRAINT clients_campaign_client_id_j3w96j6U FOREIGN KEY (client_id) REFERENCES clients_client(id);
  
  -- Table : clients_campaignday
  ALTER TABLE clients_campaignday ADD CONSTRAINT clients_campaignday_campaign_id_69svf27c FOREIGN KEY (campaign_id) REFERENCES clients_campaign(id);
  
  -- Optimize tables
  OPTIMIZE TABLE clients_campaignday, clients_campaign;
  </code></pre>
  
  <p>Now the order of execution is fixed but MySQL is still stuck on 100% and I don't know why.</p>
  
  <p>Please advice.</p>