1

Using MySQL 5.6.23, I recently changed a prepared statement from:

SELECT * FROM table WHERE BINARY column = :boundValue

to:

SELECT * FROM TABLE WHERE CAST(column AS BINARY) = :boundValue

According to the MySQL docs on BINARY:

BINARY str is shorthand for CAST(str AS BINARY)

So I expected these two prepared statements to have the same run-time performance. However, the CAST version was significantly slower. I mean orders of magnitude difference.

The query plans are identical:

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: table
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
        Extra: Using where

CREATE TABLE `session_detail_binary` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `last_updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `column` varchar(64) NOT NULL DEFAULT '',
  `foo` varchar(64) DEFAULT NULL,
  `bar` varbinary(4096) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_column` (`column`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

There are about 260k rows in this table.

What's going on here?

UPDATE: I believe this was a red herring. At the same time this SELECT .. WHERE x runs, there were a lot of UPDATE .. WHERE x on the same table and under the same where condition, where "a lot" means 100x more. This suggests to me the row locks were handicapping the selection.

  • The final, performant implementation is SELECT * FROM table WHERE column = BINARY :boundValue – bishop Jul 8 '16 at 23:03
  • Can you show the create table statement? Any chance for the column already being BINARY so the first case being no-op whereas the second one being explicit function call? – jkavalik Jul 9 '16 at 18:37
  • @jkavalik Added the table definition, but no the column is defined varchar. I believe, though, there were other factors at play and this was just a red herring -- see the OP update. – bishop Jul 14 '16 at 15:20
1

On my tests, I do not see a significant difference (full table scan, 6 rows only, like your example):

$ for i in $(seq 1 10); do for i in $(seq 1 100000); do echo "SELECT * FROM t1 WHERE CAST(c AS BINARY) = 'f';"; done | /usr/bin/time -f "%e" mysql -BN test > /dev/null; done
5.10
4.93
5.00
4.96
4.97
5.21
4.95
5.04
5.00
5.03
$ for i in $(seq 1 10); do for i in $(seq 1 100000); do echo "SELECT * FROM t1 WHERE BINARY c = 'f';"; done | /usr/bin/time -f "%e" mysql -BN test > /dev/null; done
5.10
5.28
4.84
5.00
5.19
5.17
7.14
5.06
4.86
6.24

If the table had more than 6 rows, an index (and therfore, a query plan change) would be helpful, but only if functions are applied to the right side, not to the column name:

MariaDB [test]> EXPLAIN SELECT * FROM t1 USE INDEX(c) WHERE c = BINARY 'f'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: range
possible_keys: c
          key: c
      key_len: 4
          ref: NULL
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

MariaDB [test]> EXPLAIN SELECT * FROM t1 USE INDEX(c) WHERE BINARY c = 'f'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
        Extra: Using where
1 row in set (0.00 sec)

MariaDB [test]> EXPLAIN SELECT * FROM t1 USE INDEX(c) WHERE CAST(c AS BINARY) = 'f'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
        Extra: Using where
1 row in set (0.00 sec)

MariaDB [test]> EXPLAIN SELECT * FROM t1 USE INDEX(c) WHERE c = CAST('f' AS BINARY)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t1
         type: range
possible_keys: c
          key: c
      key_len: 4
          ref: NULL
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)
| improve this answer | |
  • I've repeated these tests in an environment devoid of additional, significant UPDATE on the same table, and they agree with your results. So I conclude the locking effect of the UPDATE affected the apparent performance of the CAST AS BINARY: when the WHERE BINARY data was gathered, no UPDATE were happening. – bishop Jul 14 '16 at 17:28

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.