title: Does MySQL view always do full table scan?
body: <p>I'm trying to optimize a query which uses a view in MySQL 5.1. It seems that even if I select 1 column from the view it always does a full table scan. Is that the expected behaviour?</p>
  
  <p>The view is just a SELECT "All Columns From These Tables - NOT *" for the tables I have specified in the first query below.</p>
  
  <p>This is my explain output from when i select the indexed column PromotionID from the query which makes up the view. As you can see it is vastly different from the output on the view.</p>
  
  <pre><code>EXPLAIN SELECT pb.PromotionID FROM PromotionBase pb INNER JOIN PromotionCart pct ON pb.PromotionID = pct.PromotionID INNER JOIN PromotionCode pc ON pb.PromotionID = pc.PromotionID WHERE pc.PromotionCode = '5TAFF312C0NT'\G;
  *************************** 1. row ***************************
             id: 1
    select_type: SIMPLE
          table: pc
           type: const
  possible_keys: PRIMARY,fk_pc_pb
            key: PRIMARY
        key_len: 302
            ref: const
           rows: 1
          Extra:
  *************************** 2. row ***************************
             id: 1
    select_type: SIMPLE
          table: pb
           type: const
  possible_keys: PRIMARY
            key: PRIMARY
        key_len: 4
            ref: const
           rows: 1
          Extra: Using index
  *************************** 3. row ***************************
             id: 1
    select_type: SIMPLE
          table: pct
           type: const
  possible_keys: PRIMARY
            key: PRIMARY
        key_len: 4
            ref: const
           rows: 1
          Extra: Using index
  3 rows in set (0.00 sec)
  </code></pre>
  
  <p>The output when i select the same thing but from the view </p>
  
  <pre><code>EXPLAIN SELECT vpc.PromotionID FROM vw_PromotionCode vpc  WHERE vpc.PromotionCode = '5TAFF312C0NT'\G;
  *************************** 1. row ***************************
             id: 1
    select_type: PRIMARY
          table: &lt;derived2&gt;
           type: ALL
  possible_keys: NULL
            key: NULL
        key_len: NULL
            ref: NULL
           rows: 5830
          Extra: Using where
  *************************** 2. row ***************************
             id: 2
    select_type: DERIVED
          table: pcart
           type: index
  possible_keys: PRIMARY
            key: PRIMARY
        key_len: 4
            ref: NULL
           rows: 33
          Extra: Using index
  *************************** 3. row ***************************
             id: 2
    select_type: DERIVED
          table: pb
           type: eq_ref
  possible_keys: PRIMARY
            key: PRIMARY
        key_len: 4
            ref: readyinteractive.pcart.PromotionID
           rows: 1
          Extra:
  *************************** 4. row ***************************
             id: 2
    select_type: DERIVED
          table: pc
           type: ref
  possible_keys: fk_pc_pb
            key: fk_pc_pb
        key_len: 4
            ref: readyinteractive.pb.PromotionID
           rows: 249
          Extra: Using where
  *************************** 5. row ***************************
             id: 3
    select_type: UNION
          table: pp
           type: index
  possible_keys: PRIMARY
            key: pp_p
        key_len: 4
            ref: NULL
           rows: 1
          Extra: Using index
  *************************** 6. row ***************************
             id: 3
    select_type: UNION
          table: pb
           type: eq_ref
  possible_keys: PRIMARY
            key: PRIMARY
        key_len: 4
            ref: readyinteractive.pp.PromotionID
           rows: 1
          Extra:
  *************************** 7. row ***************************
             id: 3
    select_type: UNION
          table: pc
           type: ref
  possible_keys: fk_pc_pb
            key: fk_pc_pb
        key_len: 4
            ref: readyinteractive.pb.PromotionID
           rows: 249
          Extra: Using where
  *************************** 8. row ***************************
             id: 4
    select_type: UNION
          table: pcp
           type: index
  possible_keys: PRIMARY
            key: pcp_cp
        key_len: 4
            ref: NULL
           rows: 1
          Extra: Using index
  *************************** 9. row ***************************
             id: 4
    select_type: UNION
          table: pb
           type: eq_ref
  possible_keys: PRIMARY
            key: PRIMARY
        key_len: 4
            ref: readyinteractive.pcp.PromotionID
           rows: 1
          Extra:
  *************************** 10. row ***************************
             id: 4
    select_type: UNION
          table: pc
           type: ref
  possible_keys: fk_pc_pb
            key: fk_pc_pb
        key_len: 4
            ref: readyinteractive.pb.PromotionID
           rows: 249
          Extra: Using where
  *************************** 11. row ***************************
             id: 5
    select_type: UNION
          table: ppc
           type: index
  possible_keys: PRIMARY
            key: ppc_pc
        key_len: 4
            ref: NULL
           rows: 1
          Extra: Using index
  *************************** 12. row ***************************
             id: 5
    select_type: UNION
          table: pb
           type: eq_ref
  possible_keys: PRIMARY
            key: PRIMARY
        key_len: 4
            ref: readyinteractive.ppc.PromotionID
           rows: 1
          Extra:
  *************************** 13. row ***************************
             id: 5
    select_type: UNION
          table: pc
           type: ref
  possible_keys: fk_pc_pb
            key: fk_pc_pb
        key_len: 4
            ref: readyinteractive.pb.PromotionID
           rows: 249
          Extra: Using where
  *************************** 14. row ***************************
             id: 6
    select_type: UNION
          table: ppt
           type: index
  possible_keys: PRIMARY
            key: ppt_pt
        key_len: 4
            ref: NULL
           rows: 1
          Extra: Using index
  *************************** 15. row ***************************
             id: 6
    select_type: UNION
          table: pb
           type: eq_ref
  possible_keys: PRIMARY
            key: PRIMARY
        key_len: 4
            ref: readyinteractive.ppt.PromotionID
           rows: 1
          Extra:
  *************************** 16. row ***************************
             id: 6
    select_type: UNION
          table: pc
           type: ref
  possible_keys: fk_pc_pb
            key: fk_pc_pb
        key_len: 4
            ref: readyinteractive.pb.PromotionID
           rows: 249
          Extra: Using where
  *************************** 17. row ***************************
             id: NULL
    select_type: UNION RESULT
          table: &lt;union2,3,4,5,6&gt;
           type: ALL
  possible_keys: NULL
            key: NULL
        key_len: NULL
            ref: NULL
           rows: NULL
          Extra:
  17 rows in set (0.18 sec)
  </code></pre>