0

I am not very experienced with sql queries and looking for an efficient way to perform the same WHERE IN condition on multiple columns.

More specifically, i'm doing the following:

SELECT a, b 
FROM links 
WHERE a IN (90696, 110083, ... 147455) 
  OR b IN (90696, 110083, ... 147455)

Since both IN-lists are identical and can contain several hundreds of items, the above query just doesn't feel very right to me.

If it helps (but I don't think so, actually):

  • the following condition is always met: row[x].a < row[x].b for all rows x in Table links.

  • Im using MySQL InnoDB. Yet, I'm open to different database solutions if would significantly improve performance.

  • Currently, the items of the WHERE-IN clause are computed. However, I could also acquire them by an additional query (which would actually be the union of two queries).

Edit:

The IN-list could be constructed by

SELECT i_id as l FROM t_i WHERE id=x
UNION ALL
SELECT o_id as l FROM t_o WHERE id=x
1

As I understand you want to check each combination of a/b and i_id/o_id

then simple rewrite to join is like this:

SELECT a, b 
  FROM links
  JOIN t_i ON (a = i_id OR b = i_id)
  WHERE id = x
UNION
SELECT a, b 
  FROM links
  JOIN t_o ON (a = o_id OR b = o_id)
  WHERE id = x

But for good index usage I suggest splitting it once more. It is even longer query but can be really fast even on bigger data with indexes on t_i(id,i_id), t_o(id,o_id) links(a) and links(b)

SELECT a, b 
  FROM links
  JOIN t_i ON (a = i_id)
  WHERE id = x
UNION
SELECT a, b 
  FROM links
  JOIN t_i ON (b = i_id)
  WHERE id = x
UNION
SELECT a, b 
  FROM links
  JOIN t_o ON (a = o_id)
  WHERE id = x
UNION
SELECT a, b 
  FROM links
  JOIN t_o ON (b = o_id)
  WHERE id = x
1

Other alternative's are:

SELECT DISTINCT l.a, l.b 
FROM links l
LEFT JOIN t_i
    ON t_i.i_id IN (l.a, l.b)
LEFT JOIN t_o
    ON t_o.o_id IN (l.a, l.b)
WHERE COALESCE(t_i.i_id, t_o.o_id) IS NOT NULL;


SELECT l.a, l.b 
FROM links l
WHERE EXISTS (
    SELECT 1 
    FROM t_i 
    WHERE t_i.i_id IN (l.a, l.b)
    UNION ALL
    SELECT 1 
    FROM t_o 
    WHERE t_o.o_id IN (l.a, l.b)
);


SELECT l.a, l.b 
FROM links l
WHERE EXISTS (
    SELECT 1 
    FROM t_i 
    WHERE t_i.i_id IN (l.a, l.b)
) OR EXISTS (
    SELECT 1 
    FROM t_o 
    WHERE t_o.o_id IN (l.a, l.b)
);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.