Databases
StackOverflow2013
Postsdbo
PODoes MySQL view always do full table scan?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODoes MySQL view always do full table scan?
    primarykey
    data
    text
    <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>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USAlex
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    3. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PODoes MySQL view always do full table scan?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PODoes MySQL view always do full table scan?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PODoes MySQL view always do full table scan?
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload