SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    695
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    SELECT WHERE col1 IN() OR col2 IN()

    What's the best way to do this? We're passing a large number of IDs essentially like this: (simplified query)

    Code MySQL:
    SELECT
      *
    FROM
      table
    WHERE
      col1 IN (1, 2, 3, 4, 5...)
    OR
      col2 IN (1, 2, 3, 4, 5...)

    The actual query is quite large and joins three tables. An explain doesn't look very good:

    Code:
    id  select_type  table  type    possible_keys                    key              key_len  ref       rows   Extra
    1   SIMPLE       ph     ALL     PRIMARY,IX_basephid              NULL             NULL     NULL      36250  Using where; Using temporary
    1   SIMPLE       s      ref     phid                             phid             5        db.ph.id  1      Using index
    1   SIMPLE       p      ref     IX_products_pid,IX_products_tid  IX_products_pid  5        db.ph.id  37     Using where; Distinct
    1   SIMPLE       t      eq_ref  PRIMARY,catid                    PRIMARY          4        db.p.tid  1      Using where; Distinct
    Here's the actual query:

    Code MySQL:
    SELECT DISTINCT
        ph.id phid
    ,   ph.basephid baseid
    ,   s.id stockid
    FROM
        phones ph
    INNER
        JOIN p
        ON ph.id = p.pid
    INNER
        JOIN t
        ON t.id = p.tid
    LEFT
        JOIN s
        ON ph.id = s.phid
    WHERE
        ph.active = 1
    AND t.active  = 1
    AND t.paytype = 1
    AND t.catid   = 3
    AND (
        ph.basephid IN (1, 2 ,3 ,4, 5, 6)
    OR
        ph.id IN (1, 2, 3, 4, 5, 6)
    )

    It strikes me that the final "AND (col1 IN() OR col2 IN())" is the bit that is really slowing this down but would appreciate your advice guys.

    Thanks

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    try breaking up the query into two SELECTS, one for one IN condition and the other for the other, then combine the SELECTs with UNION

    also, are you sure you need DISTINCT?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    England
    Posts
    695
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Tried UNION and it seemed slower but may have done it badly. Will try it again.

    Also, regarding DISTINCT, we either need that or we need to GROUP BY ph.id


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •