I recently ran across an issue where I “mistakenly” used an integer in the Where clause of a MySql query against a field that had a data type of varchar (text or string). Because I did not have the schema for this particular table, I wrongly assumed the data type (which is why having a schema and proper naming conventions is important). Below is an example of what happened:
Table – Test
| Field | Type |
|---|---|
| id | int(11) |
| name | varchar(100) |
So, the query I ran was:
SELECT * FROM Test WHERE name = 123
The query still ran, but took forever. I would imagine that the MySql engine automatically casted the 123 to a string type and did the comparison. However, after realizing my mistake, the query took about 3 seconds versus a minute or so.
I thought this was interesting because I received no errors and was still allowed to run this query. So, the morale of the story, never assume anything with MySql!
-Jeffrey Shih (shihzy@gmail.com)
My real name is Jim Shih.