Performance of BINARY vs CAST AS BINARY in MySQL

Question

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
– bishop, Commented Jul 8, 2016 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
– jkavalik, Commented Jul 9, 2016 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
– bishop, Commented Jul 14, 2016 at 15:20

jynus · Accepted Answer · 2016-07-09 18:26:34Z

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)

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
– bishop, Commented Jul 14, 2016 at 17:28
Fast: 'WHERE string_column = BINARY ?' and Slow: 'WHERE BINARY string_column = ?' — goral
– goral, Commented Jan 24, 2022 at 11:56

Stack Exchange Network

Performance of BINARY vs CAST AS BINARY in MySQL

1 Answer 1

Your Answer

Hot Network Questions

Performance of BINARY vs CAST AS BINARY in MySQL

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions