In Verifying GROUP_CONCAT limit without using variables, I have presented a test to verify if group_concat_max_len is sufficient for known limitations. I will follow the path where I assume I cannot control group_concat_max_len, not even in session scope, and show an SQL solution, dirty as it is, to overcome the GROUP_CONCAT limitation, under certain conditions. [...]
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: mysql> SELECT 3 = ’3.0′; +———–+ | 3 = [...]
I happened upon a customer who left me in awe and admiration. The reason: excellent comments for their SQL code. I list four major places where SQL comments are helpful. I’ll use the sakila database. It is originally scarcely commented; I’ll present it now enhanced with comments, to illustrate. Table definitions The CREATE TABLE statement [...]
Single row tables are used in various cases. Such tables can be used for “preferences” or “settings”; for managing counters (e.g. summary tables), for general-purpose administration tasks (e.g. heartbeat table) etc. The problem with single row tables is that, well, they must have s single row. And the question is: how can you force them [...]
I have a case where I must know if group_concat_max_len is at its default value (1024), which means there are some operation I cannot work out. I’ve ranted on this here. Normally, I would simply: SELECT @@group_concat_max_len However, I am using views, where session variables are not allowed. Using a stored function can do the [...]
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 [...]
Justin’s A workaround for the performance problems of TEMPTABLE views post on mysqlperformanceblog.com reminded me of a solution I once saw on a customer’s site. The customer was using nested views structure, up to depth of some 8-9 views. There were a lot of aggregations along the way, and even the simplest query resulted with [...]
I happened on a query where, by mistake, an SELECT … ORDER BY x DESC LIMIT 1 was written as SELECT … GROUP BY x DESC LIMIT 1 And it took me by surprise to realize GROUP BY x DESC is a valid statement. I looked it up: yep! It’s documented. In MySQL, GROUP BY [...]
After seeing quite some SQL statements over the years, something is bugging me: there is no consistent convention as for how to write an SQL query. I’m going to leave formatting, upper/lower-case issues aside, and discuss a small part of the SQL syntax: table aliases. Looking at three different queries, I will describe what I [...]
TRUNCATE is usually a fast operation (much faster than DELETE FROM). But sometimes it just hangs; I’ve has several such uncheerful events with InnoDB (Plugin) tables which were extensively written to. The TRUNCATE hanged; nothing else would work; minutes pass. TRUNCATE on tables with no FOREIGN KEYs should act fast: it translate to dropping the [...]