MySQL, along with other features designed to make it easy to use, tries to do the right thing regarding strings. When you perform a math calculation on a column or columns that are of type
varchar, MySQL automatically conversts that string to a number (empty strings are treated as zero.):
However, this translation, convenient as it may be, is not free. A client of mine had a query that was running calculations against two such columns. After indexing and trying to simplify the query, we were still seeing query execution times of 2+ seconds (all times are quoted for MySQL 4.1, on my relativly slow personal laptop).
The solution appears to be to change the type of the columns using the alter table syntax to type double. After doing so and running
analyze table mytable, I was seeing query execution times of 0.2 seconds for the same query on the same box. Fantastic.
In some cases, a query can be optimized to retrieve values without consulting the data rows. If a query uses only columns from a table that are numeric and that form a leftmost prefix for some key, the selected values may be retrieved from the index tree for greater speed[.]
Regardless of the cause, if you’re doing some complicated calculations on columns, consider making them numbers.