MySQL cast sure takes its sweet time

Question

So, I ran the following in the MySQL console as a benchmark to see what was holding back the speed of my query.

SELECT bbva_deductions.ded_code, SUBSTRING_INDEX(bbva_deductions.employee_id, '-' , -1) AS tt_emplid, bbva_job.paygroup, bbva_job.file_nbr, bbva_deductions.ded_amount FROM bbva_deductions LEFT JOIN bbva_job ON CAST(SUBSTRING_INDEX(bbva_deductions.employee_id, '-' , -1) AS UNSIGNED) = bbva_job.emplid LIMIT 500 

It took about 4 seconds to start. (seems very high for only 500 lines).

The simple removal of a portion of the CAST compound decreased by 0.01 second ...

Why is Mr. Cast so slow? Am I doing something to piss off the MySQL gods here?

Edit:

According to the EXPLAIN output requested here:

And without CAST:

EXPLAIN EXTENDED:

Answer 1

As described in How MySQL Uses Indexes :

MySQL uses indexes for these operations:

  [ deletia ] 

• To get rows from other tables when performing joins. MySQL can use indexes in 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. For example, VARCHAR(10) and CHAR(10) are the same size, but VARCHAR(10) and CHAR(15) are not.

  Comparing heterogeneous columns can prevent the use of indexes if values ​​cannot be compared directly without conversion. Suppose a numeric column is compared to a row column. For a given value, such as 1 in a numeric column, it can be compared with any number of values ​​in a row column, for example '1' , ' 1' , '00001' or '01.e1' . This eliminates the use of any indexes on the row column.

In your case, you are trying to join the comparison between a substring (a row column in one table) and a row column in another table. An index can be used for this operation, however, the comparison is performed lexicographically (for example, treating operands as strings, even if they represent numbers).

By explicitly discarding one side of the integer, the comparison is performed numerically (optional), but this requires that MySQL implicitly convert the column type of the row and therefore cannot use this column index.

You are in this travel album because your circuit is poorly designed. You should strive to ensure that all columns:

• encoded using the data types most relevant to their content; and

• contains only one piece of information, see Does the delimited list in the database column really keep that bad?

At least your bbva_job.emplid should be an integer; and your bbva_deductions.employee_id should be divided so that its parts are stored in separate columns (respectively typed). With the appropriate indexes, your query will be significantly more effective.