MySQL – Selecting Multiple Rows in One Query with Multiple Conditions

MySQLselect

I can select multiple rows with one condition by using something like:

SELECT `Col`, `Col2` FROM `Table` WHERE `Col3` IN (?, ?, ?, ?, ?);
# This selects 5 rows

How can this be done if there are multiple conditions (all integer equals operations)?

There are three conditions that the query needs to check against and all three of these make up the composite primary key.

A single query will select from 10 to 100 rows (though most of the time it'll be only 10)- it has to be fast in terms of performance. This is why using multiple queries isn't a good idea.

The CREATE TABLE statement is:

CREATE TABLE `Table Name` (
  `X` smallint(6) unsigned NOT NULL,
  `Y` smallint(6) unsigned NOT NULL,
  `Z` smallint(6) unsigned NOT NULL,
  `Data` varchar(2048) NOT NULL DEFAULT '',
  PRIMARY KEY (`X`,`Y`,`Z`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

With multiple queries, it could be done like this:

SELECT `One`, `Two` FROM `Table` WHERE `X` = ? AND `Y` = ? AND `Z` = ?;
SELECT `One`, `Two` FROM `Table` WHERE `X` = ? AND `Y` = ? AND `Z` = ?;
SELECT `One`, `Two` FROM `Table` WHERE `X` = ? AND `Y` = ? AND `Z` = ?;
# This selects 3 rows but I don't want to make 3 calls to the database server for that

Best Answer

You have 2 basic syntactical options for doing this in one query and 2 options on whether to send the values in the query or load them first in a table:

  • the normal AND / OR (parentheses are redundant here but it's good to use them with OR, just in case the WHERE gets more complicated):

    WHERE (  ( x = ? AND y = ? AND z = ? )
      OR ( x = ? AND y = ? AND z = ? )
      ... 
      OR ( x = ? AND y = ? AND z = ? )
      )

  • compact IN with "row constructor":

    WHERE (x,y,z) IN ((?,?,?), (?,?,?), ...)

  • load the triplets in a (temporary) table and JOIN:

    CREATE (TEMPORARY) TABLE tmp_table 
--- ;

INSERT INTO tmp_table (x,y,z)
VALUES (?,?,?), (?,?,?), ... ;

SELECT t.*
FROM my_table AS t
  JOIN tmp_table AS tmp
  ON  ( t.x = tmp.x AND t.y = tmp.y AND t.z = tmp.z )
 ;

  • or: 

      ON  (t.x, t.y, t.z) = (tmp.x, tmp.y, tmp.z)

The first 2 options are equivalent but they may differ in efficiency, depending on version. This syntax of IN with tuples (row constructors) does not use indexes most effectively in older versions. In 5.7 the optimizer identifies the two syntaxes as equivalent (see MySQL docs: Row Constructor Expression Optimization). Test!

The other choice of using a table may be better when you want to query for a number of parameters / triplets. You can also index the (temp) table. Test!

So, the basic advice is to test in your version/configuration/setup, with the tables having sizes similar to their predicted sizes, and with various number of parameters.

Two more ways that might be worth testing too:

  • The simple UNION ALL. Since we just want to run multiple identical queries where only the parameters differ. One disadvantage is that the query gets really long and clumsy-looking if the basic query is complex and you have many triplets to check:

    SELECT t.* FROM my_table AS t
WHERE ( x = ? AND y = ? AND z = ? ) UNION ALL
SELECT t.* FROM my_table AS t
WHERE ( x = ? AND y = ? AND z = ? ) UNION ALL
---
SELECT t.* FROM my_table AS t
WHERE ( x = ? AND y = ? AND z = ? ) ;

  • Using a derived table (with UNION ALL) in the JOIN variation. This may use an optimization (that was added in 5.5 or 5.6) that can materialize and index a derived table:

    SELECT t.*
FROM my_table AS t
  JOIN 
      ( SELECT ? AS x, ? AS y, ? AS z  UNION ALL
        SELECT ?, ?, ?  UNION ALL
        ---
        SELECT ?, ?, ?
      )
    AS tmp
  ON  ( t.x = tmp.x AND t.y = tmp.y AND t.z = tmp.z )
 ;
Related Question