Skip to content

Instantly share code, notes, and snippets.

@morgo
Created September 5, 2014 00:44
Show Gist options
  • Save morgo/918b05f8fbc110ba8959 to your computer and use it in GitHub Desktop.
Save morgo/918b05f8fbc110ba8959 to your computer and use it in GitHub Desktop.
Function on index
mysql [localhost] {msandbox} (test) > CREATE TABLE cities (id int not null primary key auto_increment, name VARCHAR(50) NOT NULL, INDEX(name));
Query OK, 0 rows affected (0.05 sec)
mysql [localhost] {msandbox} (test) > INSERT INTO cities (name) VALUES ('Toronto'), ('Montreal'), ('Vancouver'), ('Calgary');
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql [localhost] {msandbox} (test) > update cities set name = AES_ENCRYPT(name, 'MYKEY');
Query OK, 4 rows affected (0.02 sec)
Rows matched: 4 Changed: 4 Warnings: 0
mysql [localhost] {msandbox} (test) > EXPLAIN SELECT * FROM cities WHERE name = AES_ENCRYPT('Toronto', 'MYKEY');
+----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | cities | NULL | ref | name | name | 52 | const | 1 | 100.00 | Using index |
+----+-------------+--------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.02 sec)
mysql [localhost] {msandbox} (test) > EXPLAIN SELECT * FROM cities WHERE AES_DECRYPT(name, 'MYKEY') = 'Toronto';
+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | cities | NULL | index | NULL | name | 52 | NULL | 4 | 100.00 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment