Can this sql query be simplified?

Question

Hi!

I have a Sql Query:

select * from contactmeta
WHERE
contactmeta.contact_id in 
(
select tob.object_id from tagobject tob, tag t, taggroup tg 
where tob.tag_id = t.tag_id
and t.tag_group_id = tg.tag_group_id
and tob.object_type_id = 14 
and tg.org_id = contactmeta.group_id 
and (t.name like '%campaign%')
)
AND
contactmeta.contact_id in 
(
select tob.object_id from tagobject tob, tag t, taggroup tg 
where tob.tag_id = t.tag_id 
and t.tag_group_id = tg.tag_group_id
and tob.object_type_id = 14 
and tg.org_id = contactmeta.group_id 
and (t.name like '%bounced%')
)

My problem is that I need to simplify the part of the query inside the WHERE clause (I cannot make another join with the contactmeta table etc.). This is because the WHERE clause is dynamically created on an otherwise static sql.

You can see that both the where condition is almost same except for the t.name condition.

Thanks for your time

SK

Answer 1

this isn't really any simpler (in terms of code) but will be more performant, since you are using joins rather than the IN operator:

select contactmeta .* from contactmeta 

inner join tagobject tob, tag t, taggroup tg  
on tob.tag_id = t.tag_id 
and t.tag_group_id = tg.tag_group_id 
and tob.object_type_id = 14  
and tg.org_id = contactmeta.group_id  
and (t.name like '%campaign%')

inner join tagobject tob2, tag t2, taggroup tg2  
on tob2.tag_id = t2.tag_id 
and t2.tag_group_id = tg2.tag_group_id 
and tob2.object_type_id = 14  
and tg2.org_id = contactmeta.group_id  
and (t2.name like '%bounced%') 

EDIT: if that is not possible (due to your comments about JOIN limitations), then you could abstract part of the logic into views e.g.

create view myView 
as
select * from tagobject tob, tag t, taggroup tg  
    on tob.tag_id = t.tag_id 
    and t.tag_group_id = tg.tag_group_id

and then use that view in your original sql.

Answer 2

Try this:

select * from contactmeta
WHERE
contactmeta.contact_id in 
(
select tob.object_id from tagobject tob, tag t, taggroup tg 
where tob.tag_id = t.tag_id
and tob.object_type_id = 14 
and tg.org_id = contactmeta.group_id 
and (
   (t.tag_group_id = tg.tag_group_id and t.name like '%campaign%')
   or
   (tob.tag_id = t.tag_id and t.name like '%bounced%')
)
)

Answer 3

As mentioned, the sub-selects in your WHERE clause are not the same.

I am unsure what you mean by "simplified", simplified in what way? Less verbose? Less complex query plan? More performance? In other words, what problem are you trying to solve?

Some ideas:

• You could create a view for your sub-select (or sub-selects if the difference between the two is on purpose) which would reduce the verboseness.

• Get the query plan of the query and look for table scans.

• Consider using full text indexing rather than LIKE with a wild card at the beginning of the pattern as this requires the entire index to be scanned (assuming there is an index on the column) -- if there isn't an index then add one.