title: MySQL optimize count query
body: <p>I've got a question about MySQL performance.
  These are my tables:</p>
  
  <p>(about 140.000 records)</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `article` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `label` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
    `title` varchar(256) COLLATE utf8_unicode_ci NOT NULL,
    `intro` text COLLATE utf8_unicode_ci NOT NULL,
    `content` text COLLATE utf8_unicode_ci NOT NULL,
    `date` int(11) NOT NULL,
    `active` int(1) NOT NULL,
    `language_id` int(11) NOT NULL,
    `category_id` int(11) NOT NULL,
    `indexed` int(1) NOT NULL,
    PRIMARY KEY (`id`)
  ) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=132911 ;
  </code></pre>
  
  <p>(about 400.000 records)</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `article_category` (
    `article_id` int(11) NOT NULL,
    `category_id` int(11) NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  </code></pre>
  
  <p>RUNNING THIS COUNT QUERY:</p>
  
  <pre><code>SELECT SQL_NO_CACHE COUNT(id) as total 
  FROM (`article`) 
  LEFT JOIN `article_category` ON `article_category`.`article_id` = `article`.`id` 
  WHERE `article`.`language_id` = 1 
  AND `article_category`.`category_id` = '&lt;catid&gt;'
  </code></pre>
  
  <p>This query takes a lot of resources, so I am wondering how to optimize this query.
  After executing it's beeing cached, so after the first run I am fine.</p>
  
  <p>RUNNING THE EXPLAIN FUNCTION:</p>
  
  <p><img src="https://i.stack.imgur.com/5cnN8.png" alt="enter image description here"></p>
  
  <p>AFTER CREATING AN INDEX:</p>
  
  <pre><code>ALTER TABLE `article_category` ADD INDEX ( `article_id` , `category_id` ) ;
  </code></pre>
  
  <p><img src="https://i.stack.imgur.com/DkR44.png" alt="enter image description here"></p>
  
  <p>After adding indexes and changing LEFT JOIN to JOIN the query runs alot faster!
  Thanks for these fast replys :)</p>
  
  <p>QUERY I USE NOW (I removed the language_id because it was not that neccesary):</p>
  
  <pre><code>SELECT COUNT(id) as total 
  FROM (`article`) 
  JOIN `article_category` ON `article_category`.`article_id` = `article`.`id` 
  AND `article_category`.`category_id` = '&lt;catid&gt;'
  </code></pre>
  
  <p>I've read something about forcing an index, but I think thats not neccesary anymore because the tables are already indexed, right?</p>
  
  <p>Thanks alot!</p>
  
  <p>Martijn</p>