Reputation: 23
I'm facing with problem in one query. The easiest will be to explain step by step:
At first I'm searching a specific values in colum1 in table1 by using query like this:
Query #1:
select column1
from table1
where column1 in('xxx','yyy','zzz')
group by column1
having count(*) >3
So now I have a list on values from column1, which occurs more than 3 times.
Then I need to use that list in where condition in another query:
select column1, column2, column3
from table1
where column1 in (query 1)
Unfortunately when I'm using query 1 as subquery, execution is really slow and I need to find a different way to this. Any suggest how can I increase a performance ?
Best regards and thank you in advance
Upvotes: 1
Views: 86
Reputation: 2546
1)First of all take a look if the query is correctly indexed.
Maybe you have to add an index on column1.
2) try with it:
select column1, column2, column3
from table1 as T1 inner join (
select column1, column2, column3
from table1
where column1 in (query 1)) as T2
on t1.column1 = t2.column1
Upvotes: 1
Reputation: 1271231
If they are the same table, then use window functions:
select t.*
from (select t.*, count(*) over (partition by column1) as cnt
from table1 t
where column1 in ('xxx', 'yyy', 'zzz')
) t
where cnt > 3;
Both this an your original query will benefit from h having an index on table1(column1)
.
Upvotes: 1