Does querying int column with string datatype have any performance impact in mysql queries?

Question

Assuming I have a table as:

create table any_table (any_column_1 int, any_column_2 varchar(255));
create index any_table_any_column_1_IDX USING BTREE ON any_table (any_column_1);

(Note: Index type should not matter here)

I was wondering if querying any_column with int or string have any impact on performance, i.e. does

select * from any_table where any_column_1 = 12345;

have any differences in terms of performance with this one?

select * from any_table where any_column_1 = '12345';

I have looked around the web and really have not faced this particular case.

Answer 1

It should be fine to do this either way for an indexed integer column. When you compare an integer column to a constant, the constant value is cast to an integer whether you format it as an integer or a string.

You can confirm this with EXPLAIN. In both cases, the EXPLAIN shows that it will use the index (type: ref indicates an index lookup), and the performance will be the same.

mysql> explain select * from any_table where any_column_1 = 12345;
+----+-------------+-----------+------------+------+----------------------------+----------------------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys              | key                        | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+----------------------------+----------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | any_table | NULL       | ref  | any_table_any_column_1_IDX | any_table_any_column_1_IDX | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+----------------------------+----------------------------+---------+-------+------+----------+-------+

mysql> explain select * from any_table where any_column_1 = '12345';
+----+-------------+-----------+------------+------+----------------------------+----------------------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys              | key                        | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+----------------------------+----------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | any_table | NULL       | ref  | any_table_any_column_1_IDX | any_table_any_column_1_IDX | 5       | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+----------------------------+----------------------------+---------+-------+------+----------+-------+

If you had indexed the string column in your example, any_column_2, it would make a difference because the collation of a string column must match the collation of the value you compare it to. A string literal will be cast to a compatible collation by default, so it uses the index:

create index any_table_any_column_2_IDX USING BTREE ON any_table (any_column_2);

mysql> explain select * from any_table where any_column_2 = '12345';
+----+-------------+-----------+------------+------+----------------------------+----------------------------+---------+-------+------+----------+-------+
| id | select_type | table     | partitions | type | possible_keys              | key                        | key_len | ref   | rows | filtered | Extra |
+----+-------------+-----------+------------+------+----------------------------+----------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | any_table | NULL       | ref  | any_table_any_column_2_IDX | any_table_any_column_2_IDX | 768     | const |    1 |   100.00 | NULL  |
+----+-------------+-----------+------------+------+----------------------------+----------------------------+---------+-------+------+----------+-------+

But an integer literal has no collation, so you get warnings, and the index cannot be used. The EXPLAIN shows type: ALL so it will do a table-scan and that will have poor performance if you query a table with many rows.

mysql> explain select * from any_table where any_column_2 = 12345;
+----+-------------+-----------+------------+------+----------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys              | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+----------------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | any_table | NULL       | ALL  | any_table_any_column_2_IDX | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+-----------+------------+------+----------------------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                                                     |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1739 | Cannot use ref access on index 'any_table_any_column_2_IDX' due to type or collation conversion on field 'any_column_2'                                                                                     |
| Warning | 1739 | Cannot use range access on index 'any_table_any_column_2_IDX' due to type or collation conversion on field 'any_column_2'                                                                                   |
| Note    | 1003 | /* select#1 */ select `test2`.`any_table`.`any_column_1` AS `any_column_1`,`test2`.`any_table`.`any_column_2` AS `any_column_2` from `test2`.`any_table` where (`test2`.`any_table`.`any_column_2` = 12345) |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+