I hope you all are in good health. I want to display the number of times a foreign key is present in its child table. For instance, I'm having a company table with a primary key 'cin' which links to five other tables and I want to know how many times each 'cin' is present in other tables.
I have made a query that works fine with small amount of data but when I run it on a database of 17GB, it takes forever (and is still running right now) where 'ov' is the name of the data base and 'opi', 'zii', 'li', 'kvi' and 'kra' are the other five tables that cin is linked as foreign key.
Select c.cin, name, br_section, address_line,
(Select count(cin) from ov.opi
where cin = c.cin) as opi_count,
(Select count(cin) from ov.zii
where cin = c.cin) as zii_count,
(Select count(cin) from li
where cin = c.cin) as li_count,
(Select count(cin) from ov.kvi
where cin = c.cin) as kvi_count,
(Select count(cin) from ov.krators
where cin = c.cin) as kra_count
from ov.company as c
group by c.cin
How can I optimize it for fast results? The output expected is somewhat like this
cin opi_count zii_count li_count kvi_count kri_count
1 56 NULL 2 NULL 9
2 NULL 140 NULL 10 23
3 2 90 10 NULL 2
4 34 NULL 89 3 NULL
5 NULL 34 2 9 NULL
. . . . . .
. . . . . .
. . . . . .
Thank you for your tme, Cheers!