MySQL slow query and type conversion

John D K -

In MySQL you can have query that it's taking much longer than expected in terms of time. This could be due to several reasons. Here we will deep in type conversion problems.

So let's check these two queries:

select id from person where emp_id = 1234567890
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 10.001 sec. */
select id from person where emp_id = '1234567890'
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 0.003 sec. */

At first glance they seems to be similar. We are getting records by foreign key and we compare to a value.

The difference is that in the first select we have the different type to the column and in the other case we have the same type. In order MySQL to deal with this it's performing type conversion for example: to_char(int).

The cause of this slow and strange query is that MySQL is doing N operation to_char(int) for:

select id from person where emp_id = 1234567890
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 10.001 sec. */

And for very big table - more than 100 million rows - this is a real problem.

So several conclusions on this:

  • be careful when you want to use string primary key - this may cause bigger and slower indexes;
  • if you need to use bigger primary keys it's better to use composite primary key - multiple columns as primary keys - this is very useful for association tables
  • be careful when you are doing a join on two columns with different type. For example varchar and int.
  • type conversion could work in unexpected way:
select 'varchar' + 77 from dual;

result:

77

  • if you have queries generated by framework - then it's better to check those queries very thoroughly

  • For MySQL and cache - The query cache is deprecated as of MySQL 5.7.20, and is removed in MySQL 8.0. - 8.10.3 The MySQL Query Cache - the reason - disabling the query cache was related in many cases to increase of MySQL Performance

  • use explain plan to check all strange queries:

explain select id from person where emp_id = 1234567890
  • pay attention to dates and warnings:
select * from test where start_date < 341232312312312312132;
/* Affected rows: 0  Found rows: 1  Warnings: 1  Duration for 1 query: 3.673 sec. */
select * from betallone.game where id < 12323 * 2 + '2';

To retrieve rows from other tables when performing joins. MySQL can use indexes on columns more efficiently if they are declared as the same type and size. In this context, VARCHAR and CHAR are considered the same if they are declared as the same size. For example, VARCHAR(10) and CHAR(10) are the same size, but VARCHAR(10) and CHAR(15) are not.

For comparisons between nonbinary string columns, both columns should use the same character set. For example, comparing a utf8 column with a latin1 column precludes use of an index.

Comparison of dissimilar columns (comparing a string column to a temporal or numeric column, for example) may prevent use of indexes if values cannot be compared directly without conversion. For a given value such as 1 in the numeric column, it might compare equal to any number of values in the string column such as '1', ' 1', '00001', or '01.e1'. This rules out use of any indexes for the string column.