MySQL seems to be happy to convert types for you. Developers are rushed to complete their project and if the function works they just move on. But what is the costs of mixing your types? Does it matter if your are running across a million rows or more? Lets find out.

Here is what the programmers see.

mysql> select 1+1;
+-----+
| 1+1 |
+-----+
|   2 |
+-----+
1 row in set (0.00 sec)

mysql> select "1"+"1";
+---------+
| "1"+"1" |
+---------+
|       2 |
+---------+
1 row in set (0.00 sec)

Benchmark

What if we do a thousand simple loops?  How long does the looping itself take?

The BENCHMARK() function executes the expression expr repeatedly count times. It may be used to time how quickly MySQL processes the expression. The result value is always 0.

mysql> select benchmark(1000000000, 1);
+--------------------------+
| benchmark(1000000000, 1) |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (5.42 sec)

mysql> select benchmark(1000000000, "1" );
+-----------------------------+
| benchmark(1000000000, "1" ) |
+-----------------------------+
|                           0 |
+-----------------------------+
1 row in set (5.40 sec)

So maybe type doesn’t matter? About five seconds just to loop but the type didn’t change it.   What if we add 1+”1″?

mysql> select benchmark(1000000000, 1+1);
+----------------------------+
| benchmark(1000000000, 1+1) |
+----------------------------+
|                          0 |
+----------------------------+
1 row in set (12.65 sec)

mysql> select benchmark(1000000000, 1+"1");
+------------------------------+
| benchmark(1000000000, 1+"1") |
+------------------------------+
|                            0 |
+------------------------------+
1 row in set (35.58 sec)
mysql> select benchmark(1000000000, "1"+"1");
+--------------------------------+
| benchmark(1000000000, "1"+"1") |
+--------------------------------+
|                              0 |
+--------------------------------+
1 row in set (51.59 sec)
It looks like type does matter.  But does it always matter?
mysql> select benchmark(1000000000, sum(1+1));
+---------------------------------+
| benchmark(1000000000, sum(1+1)) |
+---------------------------------+
|                               0 |
+---------------------------------+
1 row in set (9.69 sec)

mysql> select benchmark(1000000000, sum("1"+"1"));
+-------------------------------------+
| benchmark(1000000000, sum("1"+"1")) |
+-------------------------------------+
|                                   0 |
+-------------------------------------+
1 row in set (9.94 sec)

mysql> select benchmark(1000000000, sum("1.23456789"+"1.23456789"));
+-------------------------------------------------------+
| benchmark(1000000000, sum("1.23456789"+"1.23456789")) |
+-------------------------------------------------------+
|                                                     0 |
+-------------------------------------------------------+
1 row in set (10.32 sec)
So, not all functions are the same.  But it looks like size might matter!
mysql> select benchmark(1000000000, 1.1+1.1);
+--------------------------------+
| benchmark(1000000000, 1.1+1.1) |
+--------------------------------+
|                              0 |
+--------------------------------+
1 row in set (34.90 sec)

mysql> select benchmark(1000000000, "1.1"+"1.1");
+------------------------------------+
| benchmark(1000000000, "1.1"+"1.1") |
+------------------------------------+
|                                  0 |
+------------------------------------+
1 row in set (1 min 15.32 sec)

mysql> select  benchmark(1000000000, "1.123456789"+"1.123456789");
+----------------------------------------------------+
| benchmark(1000000000, "1.123456789"+"1.123456789") |
+----------------------------------------------------+
|                                                  0 |
+----------------------------------------------------+
1 row in set (1 min 53.32 sec)
Sorry.  Looks like size does matter.

This doesn't seem logical.
mysql> select benchmark(1000000000, 1=1);
+----------------------------+
| benchmark(1000000000, 1=1) |
+----------------------------+
|                          0 |
+----------------------------+
1 row in set (12.75 sec)

mysql> select benchmark(1000000000, 1="1");
+------------------------------+
| benchmark(1000000000, 1="1") |
+------------------------------+
|                            0 |
+------------------------------+
1 row in set (40.78 sec)
mysql> select benchmark(1000000000, 1=true);
+-------------------------------+
| benchmark(1000000000, 1=true) |
+-------------------------------+
|                             0 |
+-------------------------------+
1 row in set (12.73 sec)

mysql> select benchmark(1000000000, 1="true");
+---------------------------------+
| benchmark(1000000000, 1="true") |
+---------------------------------+
|                               0 |
+---------------------------------+
1 row in set, 65535 warnings (3 min 5.72 sec)
mysql> select benchmark(1000000000, "true"="true");
+--------------------------------------+
| benchmark(1000000000, "true"="true") |
+--------------------------------------+
|                                    0 |
+--------------------------------------+
1 row in set (57.25 sec)
Maybe we should CAST our work?
mysql> select benchmark(1000000000, cast("1" as unsigned));
+----------------------------------------------+
| benchmark(1000000000, cast("1" as unsigned)) |
+----------------------------------------------+
|                                            0 |
+----------------------------------------------+
1 row in set (32.27 sec)

mysql> select benchmark(1000000000, cast("1" as unsigned) + cast("1" as unsigned));
+----------------------------------------------------------------------+
| benchmark(1000000000, cast("1" as unsigned) + cast("1" as unsigned)) |
+----------------------------------------------------------------------+
|                                                                    0 |
+----------------------------------------------------------------------+
1 row in set (1 min 7.24 sec)

Maybe not!
Conclusion:  Be careful with your data types.  If you are taking user input, do the type conversion ONCE in your program.  Don’t let MySQL do the type conversions for you.
query = “SELECT * FROM table where $INPUT = 1″;   could be doing your wrong.

References:

http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_benchmark

http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html

http://dev.mysql.com/doc/refman/5.0/en/cast-functions.html

Tweet