MySQL view performance

Question

I have a table for about 100,000 users in it.

First Case:

explain select state, count(*) as cnt from users where state = 'ca'

When I do an explain plan for the above query I get the cost as 5200

Second Case:

Create or replace view vw_users as select state, count(*) as cnt from users

Explain select cnt from vw_users where state = 'ca'

When I do an explain plan on the second query I get the cost as 100,000.

How does the where clause in the view work? Is the where clause applied after the view retrieves all the rows? How do I fix this issue?

Answer 1

It's about the view algorithm that's been used.

The merge algorithm works well most table indexes and whatnot - the temptable algorithm doesn't - in many cases your indexes will just be flat-out not used at all.

And there's lots of crap that merge doesn't support

MERGE cannot be used if the view contains any of the following constructs:

* Aggregate functions (SUM(), MIN(), MAX(), COUNT(), and so forth)
* DISTINCT
* GROUP BY
* HAVING
* LIMIT
* UNION or UNION ALL
* Subquery in the select list
* Refers only to literal values (in this case, there is no underlying table)