Performance of BINARY vs CAST AS BINARY in MySQL 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.
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 &gt; /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 &gt; /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]&gt; 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]&gt; 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]&gt; 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]&gt; 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)