MySQL is known for its willingness to accept invalid queries, data values. It can silently commit your transaction, truncate your data.
Using GROUP_CONCAT with a small group_concat_max_len setting? Your result will be silently truncated (make sure to check the warnings though).
Calling CREATE TEMPORARY TABLE? You get silent commit.
Issuing a ROLLBACK on non-transactional involved engines? Have a [...]
Here’s a complication of some common and useful time & date calculations and equations. Some, though very simple, are often misunderstood, leading to inefficient or incorrect implementations.
There are many ways to solve such problems. I’ll present my favorites.
Querying for time difference
Given two timestamps: ts1 (older) and ts2 (newer), how much time has passed between them?
One [...]
Unless specified otherwise, latin1 is the default character set in MySQL.
What I usually find in schemes are columns which are either utf8 or latin1. The utf8 columns being those which need to contain multilingual characters (user names, addresses, articles etc.), and latin1 column being all the rest (passwords, digests, email addresses, hard-coded values etc.)
I find [...]
InnoDB and MyISAM use B+ and B trees for indexes (InnoDB also has internal hash index).
In both these structures, the depth of the index is an important factor. When looking for an indexed row, a search is made on the index, from root to leaves.
Assuming the index is not in memory, the depth of the [...]
MySQL’s character sets and collations are often considered as a mystery, and many users either completely disregard them and keep with the defaults, or set everything to UTF8.
This post will attempt to shed some light on the mystery, and provide with some best practices for use with text columns with regard to character sets.
During my work with companies using MySQL, I have encountered many issues with regard to schema design, normalization and indexing. Of the most common errors are incorrect data types definition.
Here’s a compilation of “the right and the wrong” data types.