title: What is the "filtered" column in MySQL EXPLAIN telling me, and how can I make use of it?
body: <p>The <a href="https://dev.mysql.com/doc/refman/5.7/en/explain-output.html" rel="noreferrer">MySQL 5.7 documentation</a> states:</p>
  
  <blockquote>
    <p>The <code>filtered</code> column indicates an estimated percentage of table rows that will be filtered by the table condition. That is, <code>rows</code> shows the estimated number of rows examined and <code>rows × filtered / 100</code> shows the number of rows that will be joined with previous tables.</p>
  </blockquote>
  
  <p>To attempt to understand this better, I tried it out on a query using the <a href="https://dev.mysql.com/doc/sakila/en/" rel="noreferrer">MySQL Sakila Sample Database</a>. The table in question has the following structure:</p>
  
  <pre><code>mysql&gt; SHOW CREATE TABLE film \G
  *************************** 1. row ***************************
         Table: film
  Create Table: CREATE TABLE `film` (
    `film_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
    `title` varchar(255) NOT NULL,
    `description` text,
    `release_year` year(4) DEFAULT NULL,
    `language_id` tinyint(3) unsigned NOT NULL,
    `original_language_id` tinyint(3) unsigned DEFAULT NULL,
    `rental_duration` tinyint(3) unsigned NOT NULL DEFAULT '3',
    `rental_rate` decimal(4,2) NOT NULL DEFAULT '4.99',
    `length` smallint(5) unsigned DEFAULT NULL,
    `replacement_cost` decimal(5,2) NOT NULL DEFAULT '19.99',
    `rating` enum('G','PG','PG-13','R','NC-17') DEFAULT 'G',
    `special_features` set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') DEFAULT NULL,
    `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`film_id`),
    KEY `idx_title` (`title`),
    KEY `idx_fk_language_id` (`language_id`),
    KEY `idx_fk_original_language_id` (`original_language_id`),
    CONSTRAINT `fk_film_language` FOREIGN KEY (`language_id`) REFERENCES `language` (`language_id`) ON UPDATE CASCADE,
    CONSTRAINT `fk_film_language_original` FOREIGN KEY (`original_language_id`) REFERENCES `language` (`language_id`) ON UPDATE CASCADE
  ) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8
  </code></pre>
  
  <p>And this is the <code>EXPLAIN</code> plan for the query:</p>
  
  <pre><code>mysql&gt; EXPLAIN SELECT * FROM film WHERE release_year=2006 \G
  *************************** 1. row ***************************
             id: 1
    select_type: SIMPLE
          table: film
     partitions: NULL
           type: ALL
  possible_keys: NULL
            key: NULL
        key_len: NULL
            ref: NULL
           rows: 1000
       filtered: 10.00
          Extra: Using where
  </code></pre>
  
  <p>This table's sample dataset has 1,000 total rows, and all of them have <code>release_year</code> set to 2006. Using the formula in the MySQL documentation:</p>
  
  <p><code>rows x filtered / 100</code> = "number of rows that will be joined with previous tables</p>
  
  <p>So,</p>
  
  <p><code>1,000 x 10 / 100 = 100</code> = "100 rows will be joined with previous tables"</p>
  
  <p>Huh? What "previous table"? There is no <code>JOIN</code> going on here.</p>
  
  <p>What about the first portion of the quote from the documentation? "Estimated percentage of table rows that will be filtered by the table condition." Well, the table condition is <code>release_year = 2006</code>, and <em>all</em> records have that value, so shouldn't <code>filtered</code> be either <code>0.00</code> or <code>100.00</code> (depending on what they mean by "filtered")?</p>
  
  <p>Maybe it's behaving strangely because there's no index on <code>release_year</code>? So I created one:</p>
  
  <pre><code>mysql&gt; CREATE INDEX test ON film(release_year);
  </code></pre>
  
  <p>The <code>filtered</code> column now shows <code>100.00</code>. So, shouldn't it have shown <code>0.00</code> before I added the index? Hm. What if I make half the table have <code>release_year</code> be 2006, and the other half not?</p>
  
  <pre><code>mysql&gt; UPDATE film SET release_year=2017 ORDER BY RAND() LIMIT 500;
  Query OK, 500 rows affected (0.03 sec)
  Rows matched: 500  Changed: 500  Warnings: 0
  </code></pre>
  
  <p>Now the <code>EXPLAIN</code> looks like this:</p>
  
  <pre><code>mysql&gt; EXPLAIN SELECT * FROM film WHERE release_year=2006 \G
  *************************** 1. row ***************************
             id: 1
    select_type: SIMPLE
          table: film
     partitions: NULL
           type: ref
  possible_keys: test
            key: test
        key_len: 2
            ref: const
           rows: 500
       filtered: 100.00
          Extra: Using index condition
  </code></pre>
  
  <p>And, since I decided to confuse myself even further:</p>
  
  <pre><code>mysql&gt; EXPLAIN SELECT * FROM film WHERE release_year!=2006 \G
  *************************** 1. row ***************************
             id: 1
    select_type: SIMPLE
          table: film
     partitions: NULL
           type: ALL
  possible_keys: test
            key: NULL
        key_len: NULL
            ref: NULL
           rows: 1000
       filtered: 50.10
          Extra: Using where
  </code></pre>
  
  <p>So, an estimate of 501 rows will be filtered by the table condition and "joined with previous tables"?</p>
  
  <p>I simply do not understand.</p>
  
  <p>I realize it's an "estimate", but on what is this estimate based? If an index being present moves the estimate to <code>100.00</code>, shouldn't its absence be <code>0.00</code>, not <code>10.00</code>? And what's with that <code>50.10</code> result in the last query?</p>
  
  <p>Is <code>filtered</code> at all useful in determining if a query can be optimized further, or <em>how</em> to optimize it further, or is it generally just "noise" that can be ignored?</p>