18

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?

Peter O.
  • 32,158
  • 14
  • 82
  • 96
vamsivanka
  • 792
  • 7
  • 16
  • 36
  • 2
    Your view definition is missing the group by clause. – Ike Walker May 03 '10 at 19:54
  • If you can't use the merge algorithm, here's a work around I found helpful: [Workaround for the performance problems of temptable views](http://www.mysqlperformanceblog.com/2010/05/19/a-workaround-for-the-performance-problems-of-temptable-views/) – landoncz Jul 14 '11 at 15:38

1 Answers1

30

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)
Peter Bailey
  • 105,256
  • 31
  • 182
  • 206
  • Peter, Thanks for the input. I have a count(*) in my view. So i can't use merge. Right now i didn't specify any algorithm type. So the best way can be use a stored procedure instead of a view ? – vamsivanka May 03 '10 at 19:29
  • Yeah, you'll have to go for a stored procedure here - or just a straight-up subquery. – Peter Bailey May 03 '10 at 19:31