title: Why is this query examining so many rows, when the EXPLAIN looks so good?
body: <p>I have the following query:</p>
  
  <pre><code>SELECT  mdg.id AS mdg_id, vdk.id AS vdisk_id, vdk.name AS vdisk_name, vdk.objKey AS vdisk_key,
          vdc.type AS copy_type, vde.copy_id AS copy_id, mdk.id AS mdisk_id, vde.number_extents AS extent_count,
          IFNULL(ter.name,'generic_hdd') AS mdisk_tier, mdg.snapKey, vde.objKey AS row_key
      FROM       mdisk_grp   AS mdg
      INNER JOIN vdiskcopy   AS vdc ON mdg.snapKey = vdc.snapKey AND mdg.id = vdc.mdisk_grp_id
      INNER JOIN vdisk       AS vdk ON vdc.owner = vdk.objKey
      INNER JOIN vdiskextent AS vde ON vdk.snapKey=vde.snapKey AND vdk.id = vde.vdisk_id AND vdc.copy_id = vde.copy_id
      INNER JOIN mdisk       AS mdk ON vde.id = mdk.id AND vde.snapKey = mdk.snapKey
      LEFT  JOIN tier_mdisk  AS tmk ON tmk.mdisk_objKey = mdk.objKey
      LEFT  JOIN tier        AS ter ON tmk.tier_objKey = ter.objKey
      WHERE mdg.snapKey= '333';
  </code></pre>
  
  <p>I'm seeing it frequently in the slow query log (witch different values of mdg.snapKey, of course); for example:</p>
  
  <pre><code># User@Host: svcControl[svcControl] @ localhost []
  # Query_time: 2577  Lock_time: 0  Rows_sent: 11469  Rows_examined: 354942843
  </code></pre>
  
  <p>The fact that it is running long isn't surprising, given the number of rows being examined. </p>
  
  <p>However, when I <code>EXPLAIN</code> the query, I see</p>
  
  <pre><code>+----+-------------+-------+--------+---------------------------------------------------------+--------------------+---------+------------------------+------+---------------------+
  | id | select_type | table | type   | possible_keys                                           | key                | key_len | ref                    | rows | Extra               |
  +----+-------------+-------+--------+---------------------------------------------------------+--------------------+---------+------------------------+------+---------------------+
  |  1 | SIMPLE      | tmk   | system | mdisk_objKey_idx                                        | NULL               | NULL    | NULL                   |    0 | const row not found |
  |  1 | SIMPLE      | ter   | const  | PRIMARY                                                 | NULL               | NULL    | NULL                   |    1 |                     |
  |  1 | SIMPLE      | mdg   | ref    | mdisk_grp_id_idx,snapIdx                                | snapIdx            | 2       | const                  |   11 |                     |
  |  1 | SIMPLE      | vdc   | ref    | snapIdx,mdgIdIdx,ownerIdx                               | mdgIdIdx           | 5       | svcObjects.mdg.id      |    1 | Using where         |
  |  1 | SIMPLE      | vdk   | eq_ref | PRIMARY,vdisk_id_idx,snapIdx                            | PRIMARY            | 3       | svcObjects.vdc.owner   |    1 |                     |
  |  1 | SIMPLE      | mdk   | ref    | mdisk_id_idx,snapKey_idx                                | snapKey_idx        | 2       | svcObjects.vdk.snapKey |   16 |                     |
  |  1 | SIMPLE      | vde   | ref    | vdiskextent_id_idx,vdiskextent_vdisk_id_idx,snapKey_idx | vdiskextent_id_idx | 5       | svcObjects.mdk.id      |   23 | Using where         |
  +----+-------------+-------+--------+---------------------------------------------------------+--------------------+---------+------------------------+------+---------------------+
  </code></pre>
  
  <p>Multiplying all of those rows together comes to a little over 4000 (ignoring the 0 in the column)</p>
  
  <p>It <em>appears</em> that the execution plan is being ignored.</p>
  
  <p>Is there a way to run a query and then get a report and what execution plan was actually followed? Or am I misunderstanding this output?</p>
  
  <p><strong>UPDATE</strong>
  I noticed that the Cardinality for some of my indexes was NULL, so I <code>ANALYZE</code>d all of the tables. The latest <code>EXPLAIN</code> reports:</p>
  
  <pre><code>+----+-------------+-------+--------+---------------------------------------------------------+--------------------------+---------+----------------------+------+---------------------+
  | id | select_type | table | type   | possible_keys                                           | key                      | key_len | ref                  | rows | Extra               |
  +----+-------------+-------+--------+---------------------------------------------------------+--------------------------+---------+----------------------+------+---------------------+
  |  1 | SIMPLE      | tmk   | system | mdisk_objKey_idx                                        | NULL                     | NULL    | NULL                 |    0 | const row not found |
  |  1 | SIMPLE      | ter   | const  | PRIMARY                                                 | NULL                     | NULL    | NULL                 |    1 |                     |
  |  1 | SIMPLE      | mdg   | ref    | mdisk_grp_id_idx,snapIdx                                | snapIdx                  | 2       | const                |    8 |                     |
  |  1 | SIMPLE      | vdc   | ref    | snapIdx,mdgIdIdx,ownerIdx                               | snapIdx                  | 2       | const                | 1580 | Using where         |
  |  1 | SIMPLE      | vdk   | eq_ref | PRIMARY,vdisk_id_idx,snapIdx                            | PRIMARY                  | 3       | svcObjects.vdc.owner |    1 |                     |
  |  1 | SIMPLE      | vde   | ref    | vdiskextent_id_idx,vdiskextent_vdisk_id_idx,snapKey_idx | vdiskextent_vdisk_id_idx | 5       | svcObjects.vdk.id    |  300 | Using where         |
  |  1 | SIMPLE      | mdk   | ref    | mdisk_id_idx,snapKey_idx                                | mdisk_id_idx             | 5       | svcObjects.vde.id    |   14 | Using where         |
  +----+-------------+-------+--------+---------------------------------------------------------+--------------------------+---------+----------------------+------+---------------------+
  </code></pre>
  
  <p>That multiplies up to 53,088,000, which is much better than the 354,942,843 that were examined in the slow query.</p>
  
  <p>I guess my question now is this... does the behaviour that I've seen get explained by what I did... if the index cardinalities are NULL then the execution plan will appear over-optimistic and may also result in 6-7 times the number of necessary rows being examined?</p>