casting - MySQL CAST sure takes its sweet time -

so ran following in mysql console control test see holding speed of query.

select bbva_deductions.ded_code, substring_index(bbva_deductions.employee_id, '-' , -1) tt_emplid,              bbva_job.paygroup, bbva_job.file_nbr, bbva_deductions.ded_amount              bbva_deductions              left join bbva_job              on cast(substring_index(bbva_deductions.employee_id, '-' , -1) unsigned) = bbva_job.emplid limit 500 

it took consistently around 4 seconds run. (seems high 500 rows).

simply removing cast part of join decreased 0.01 seconds...

why on earth mr.cast slow? doing anger mysql gods here?

edit:

as requested here explain output:

and without cast:

explain extended:

as documented under how mysql uses indexes:

mysql uses indexes these operations:

[ deletia ]

• to retrieve rows other tables when performing joins. mysql can use indexes on columns more efficiently if declared same type , size. in context, varchar , char considered same if declared same size. example, varchar(10) , char(10) same size, varchar(10) , char(15) not.

  comparison of dissimilar columns may prevent use of indexes if values cannot compared directly without conversion. suppose numeric column compared string column. given value such 1 in numeric column, might compare equal number of values in string column such '1', ' 1', '00001', or '01.e1'. rules out use of indexes string column.

in case, attempting join on comparison between substring (of string column in 1 table) , string column in table. index can used operation, comparison performed lexicographically (i.e. treating operands strings, if represent numbers).

by explicitly casting 1 side integer, comparison performed numerically (as desired) - requires mysql implicitly convert type of string column , therefore unable use column's index.

you have hit roadbump because schema poorly designed. should strive ensure columns:

1. are encoded using data types relevant content; and

2. contain single piece of information—see is storing delimited list in database column bad?

at least, bbva_job.emplid should integer; , bbva_deductions.employee_id should split parts stored in separate (appropriately-typed) columns. appropriate indexes, query considerably more performant.