Avatar of LeTay

asked on 

Optimise a SQL query

I have two SQL tables, says T1 and T2
T1 has PK1 as primary key and a column C1
T2 has K1 as foreign key PK1 in T1 and a column C1
I need to count the number of T1.PK1 for which  T1.C1 has a given value (3) and for which there is at least on T2.C1 > 0
My current select is

select   count(distinct T1.PK1)
from    T1, T2
where  T1.PK1 = T2.K1
and      T1.C1 = 3
and      T2.C1 > 0

I think that it is possible to optimise this as I presume that the entire T2 table is scanned for those rows where T2.C1 = T1.C1 (and T1.C1 = 3) ...

Avatar of undefined
Last Comment
Pavel Celba

8/22/2022 - Mon