Mysql – Multiple WHERE IN on multiple columns

innodbMySQLoptimizationperformance

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

Best Answer

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
Related Question