7

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
| improve this question | | | | |
11

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 )
     ;
    
| improve this answer | | | | |
  • Normal AND/OR: 1.0-1.2ms for 15 triplets/rows Compact IN: 0.6-0.8ms for 15 triplets/rows They both seem to have 35ms in the first query, which is really strange. I'll test it with a temporary table tomorrow and get back to you. Thanks! – J. Doe Oct 16 '16 at 20:51
  • 1
    If the first execution is slower than the rest, then it's a cache effect, so the next executions are fast only because the data is already in memory or MySQL has cached the query result. You should use SQL_NO_CACHE or run multiple queries with randomized/different parameters. – ypercubeᵀᴹ Oct 16 '16 at 20:54
1

A note for MariaDB. Row Constructor Expression Optimization DOES NOT WORK! My version is 10.1.24 and I don't see it mentioned in the 10.2 notes either! I did not test it for a PRIMARY but I did test for UNIQUE and normal INDEX.

Also, if you are using AND/OR and the query length is getting high (at aprox 1200 conditions - not an exact figure) then the query analyzer fails to pick the proper index so you need to add a FORCE INDEX clause in your QUERY.

Long queries - bad analyze. Row constructor would have saved the day.

| improve this answer | | | | |

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.