How would you check whether some TIMESTAMP column falls within a given day, say July 26th, 2011? This is a question I tend to ask students, and usually I get the same range of answers. Some are wrong; some cannot utilize an index, some are correct, and some are temporarily correct. I wish to take [...]
Today I have released common_schema, a utility schema for MySQL which includes many views and functions, and is aimed to be installed on any MySQL server. What does it do? There are views answering for all sorts of useful information: stuff related to schema analysis, data dimensions, monitoring, processes & transactions, security, internals... There are [...]
They both look the same, have roughly the same use. Nevertheless, they differ in many respects. I'll note the difference, and note a few pitfalls and peculiarities. Range TIMESTAMP starts with the epoch, '1970-01-01 00:00:01' UTC and ends with '2038-01-19 03:14:07' UTC. This is all very nice today, and may actually hold up till our [...]
In Beware of implicit casting, I have outlined the dangers of implicit casting. Here's a few more real-world examples I have tackled: Number-String comparisons Much like in programming languages, implicit casting is made to numbers when at least one of the arguments is a number. Thus: The second query consists of pure strings comparison. It [...]
How do you implement True/False columns? There are many ways to do it, each with its own pros and cons. ENUM Create you column as ENUM('F', 'T'), or ENUM('N','Y') or ENUM('0', '1'). This is the method used in the mysql tables (e.g. mysql.user privileges table). It's very simple and intuitive. It truly restricts the values [...]
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 [...]
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 [...]
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.) [...]
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 [...]
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.