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 got 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 got 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. Please let know, how can i fix this issue. Thanks

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)