[thelist] SQL SELECT speed-up question

Jay Turley jayturley at gmail.com
Thu Dec 6 16:10:47 CST 2007


Matt, the sql I posted in my initial email is about as clear and logical as
I can make it. (a) find all the contact_id matching the inv_type and
inv_range values in the IN statements, then (b) join those against the
contacts table. I will repost code below just in case.

select
    c.contact_id
    ,c.fname
    ,c.lname
from
    econtacts c
inner join
    (
    select
        i.contact_id
    from
        einv_interests i
    where
        i.inv_type_id in
(10,61,60,55,56,57,58,59,50,51,52,41,42,44,43,45,46,47,48,49)
    and
        i.inv_range_id in (3,4)
    ) ci
on
    c.contact_id = ci.contact_id


On Dec 6, 2007 3:06 PM, Matt Warden <mwarden at gmail.com> wrote:

> On 12/6/07, Jay Turley <jayturley at gmail.com> wrote:
> > I have tried using temporary tables, derived tables, UNIONs, and a bunch
> of
> > other things that didn't work; nothing will speed it up more than a few
> > seconds. I am not an expert in SQL, so I feel there must be something I
> am
> > missing about how to approach this. Here is sample code using derived
> tables
> > and IN:
>
> Do you have a formulation of the query that is most logical and
> ignores potential performance issues? It will be easier for us to
> start from there.
>



More information about the thelist mailing list