5

I have a view (viewX) based on joins of some tables:

When I use WHERE, the query is delayed, processor usage goes to 50% and finally I need to close mysqld.exe service and restart to try to solve the problem again.

When I use HAVING, the query executes perfectly and quickly, I get the results and all is ready.

The query is similar to this:

SELECT * FROM viewX WHERE column_of_view = 'foo'

SELECT * FROM viewX HAVING column_of_view = 'foo'

What is happening?

The solution I found is to do something like this:

SELECT * FROM (SELECT * FROM viewX) as T WHERE column_of_view = 'foo'

SELECT * FROM (SELECT * FROM viewX) as T HAVING column_of_view = 'foo'

BOTH QUERIES WORKS FINE, BUT, I think this is BAD! (SELECT * FROM (...viewX)????)

CRISHK Corporation
  • 2,948
  • 6
  • 37
  • 52
  • can you post some execution plans? – Thilo Sep 16 '11 at 03:40
  • My best guess is that by using HAVING you are confusing the query optimizer just like you are confusing me, and that this confusion results in a different execution plan (different join order and methods), which in this case may actually be beneficial (with many joins, the potential for a suboptimal join order is huge). Does it just return the first few rows faster, or is the whole select for all rows also faster? – Thilo Sep 16 '11 at 03:43
  • What happens if you specify both? – Thilo Sep 16 '11 at 03:50
  • Does the view have an aggregate already? – gbn Sep 16 '11 at 05:01

4 Answers4

5

WHERE is for filtering query results based on condition.

HAVING is for applying a filter on results of an aggregate function. In absence of aggregate function it functions same as WHERE.

http://blog.sqlauthority.com/2007/07/04/sql-server-definition-comparison-and-difference-between-having-and-where-clause/

bluish
  • 26,356
  • 27
  • 122
  • 180
Muhammad Hasan Khan
  • 34,648
  • 16
  • 88
  • 131
  • 1
    That is correct, but does not explain what is happening. In fact, it argues that there should be no difference. – Thilo Sep 16 '11 at 03:49
1

Having is used for aggregate functions like sum,avg so on and it can be using only in select statement.where clause won't work in aggregate conditions eg: where sum(mark) > 300 // its not true

Bhargav Rao
  • 50,140
  • 28
  • 121
  • 140
Mohammedshafeek C S
  • 1,916
  • 2
  • 16
  • 26
0

It depends on the definition of the view - having clause should only apply to an aggregate query and its applied after the grouping. Have you looked at the two query plans (with explain)?

Lee
  • 345
  • 2
  • 4
0

WHERE is used while listing and no ALIAS names are available yet

HAVING filters rows after listing all possible rows so ALIAS names are generated

There should be a problem while filtering rows inline.

AMIB
  • 3,262
  • 3
  • 20
  • 20