Query taking more time

Question

I have a Master table.This table contains two fields

Listings(Master Table)
--------
TableID        EntityID

30047             100
30047             101
30047             102

In above table TableID (30047) contains 1.5 million entityids (103,104,105......) Total count of records nearly 10 million.

Bindings(ChildTable)



 Entityid      Fid       Value
    100           7100      JK
    101           7100      JK
    102           7100      JK
    103           7101      VV
    104           7101      VV
    105           7101      VV
    106           7102      22-nov-2017
    107           7102      22-nov-2017





ABOVE TABLE fid 7100 contains 140000 records and it is value is 'JK'
                7101 contains 120000 records and it is value is 'vv'
                7102 contains 10000 records and  it is value is '22-nov-17'
                i NEED below output.

o/p

COUNT(1)    VALUE
140000       JK
120000       VV 
100000       22-nov-17

I tried below queries. But it is not working and also taking a lot of time to execute. I created index on fid and entityid and value columns

 select count(1),vb.Value from Listings el
      inner join   Bindings vb on vb.fieldid=7100 and el.EntityID=vb.EntityID   
      where TableID=30047 group by vb.value

     select count(1) count,vb.Value from Listings el
     inner join   Bindings vb on vb.fieldid=7102 and el.EntityID=vb.EntityID and vb.value in ('22-Nov-2017')   
     inner join   ValueBindings vb1 on vb.fieldid=7100 and el.EntityID=vb1.EntityID and vb1.value in ('JK')   
     where TableID=30047 group by vb.value

      select count(1),vb.Value from Listings el
     inner join Bindings vb on  el.EntityID=vb.EntityID    
      where TableID=30047 
     and exists (select 1 from Bindings v2 where vb.EntityID = v2.EntityID and v2.FieldID=7100 and(vb.value IN('jk')))
      -- and exists (select 1 from Bindings v2 where vb.EntityID = v2.EntityID and v2.FieldID=7102 and(vb.value IN('22-Nov-2017')))
     --and exists (select 1 from Bindings v3 where vb.EntityID = v3.EntityID and v3.FieldID=7101 and(vbvalue IN('VV')))
      group by vb.value 

Answer 1

Try:

Select Count(*) as counts, Value from Bindings 
    where EntityID in (Select EntityId from Listings where TableID=30047)
    Group by Value

(Also, for indexing, you want to index Bindings on both EntityID and Value, in that order, and index Listings on Tableid and EntityID, in that order.)