Does MySQL view always do full table scan?

Question

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?

The view is just a SELECT "All Columns From These Tables - NOT *" for the tables I have specified in the first query below.

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.

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)

The output when i select the same thing but from the view

EXPLAIN SELECT vpc.PromotionID FROM vw_PromotionCode vpc  WHERE vpc.PromotionCode = '5TAFF312C0NT'\G;
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         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: <union2,3,4,5,6>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra:
17 rows in set (0.18 sec)

Ursala

Answer 1

Views in MySQL are not indexed so by their very nature require a full scan each time they are accessed. Generally speaking this makes Views really only useful for situations where you have a fairly complex static query that returns a small result set and you plan to grab the entire result set every time.

Edit: Of course Views will use the indexes on the underlying tables so that the View itself is optimized (otherwise they wouldn't make any sense at all to use) but because there are no indexes on a View it is not possible for a WHERE query on the View to be optimized.

Constructing indexes for Views would be expensive anyway because while I've not tried to profile any Views, I'm fairly certain that a temp table is constructed behind the scenes and then the result set returned. It already takes plenty of time to construct the temp table, I wouldn't want a view that also tries to guess what indexes are needed. Which brings up the second point which is that MySQL does not currently offer a method to specify what indexes to use for a View so how does it know what fields need to be indexed? Does it guess based on your query?

You might consider using a Temporary Table because then you can specify indexes on fields in the temporary table. However, from experience this tends to be really, really slow.

If all this view contains is a SELECT ALL FROM table1, table2, table3; then I would have to ask why this query needs to be in a View at all? If for some reason its absolutely necessary, you might want to use a stored procedure to encapsulate the query as you'll then be able to get optimized performance while maintaining the benefit of a simpler call to the database for the result set.

Sudatory

It says here dev.mysql.com/doc/refman/5.0/en/view-restrictions.html that the view will use the indexes of the underlying tables. – Ursala

I am pretty sure you are right - queries on MySQL views can use indexes from its source tables. You just can't have an index on the view itself and hence cannot have a single index containing columns from more than one of those tables. – Readily

Unfortunately that doesn't explain why that second query is so poorly optimised by MySQL. All I can think of is maybe that view isn't exactly joining in the way it is intended to. Don't know though. – Readily

If you use a WHERE statement on a View then a full table scan has to be performed to find the values that match the WHERE clause because the View itself is not indexed. – Sudatory

Answer 2

I've looked deeper into it an I've missed a key point of information :( My view query actually has a union with another table. This is causing the view to use the TEMPORARY TABLE algorithm instead of the MERGE algorithm.

The TEMPORARY TABLE algorithm doesn't allow the use of indexes in the underlying tables.

This seems to be a bug in MySQL and was reported way back in 2006 but doesn't look like it has been solved in 2009! http://forums.mysql.com/read.php?100,56681,56681

Looks like i'm just going to have to re-write the query as an outer join.

Ursala