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 [...]
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 [...]
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 [...]
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 [...]
Sometimes MySQL gets it wrong. It doesn’t use the right index. It happens that MySQL generates a query plan which is really bad (EXPLAIN says it’s going to explore some 10,000,000 rows), when another plan (soon to show how was generated) says: “Sure, I can do that with 100 rows using a key”. A true [...]
And the following query: SELECT `;`.`*`.`.` FROM `;`.`*`; is valid as well. So are the following: DROP DATABASE IF EXISTS `;`; CREATE DATABASE `;`; CREATE TABLE `;`.`*` (`.` INT); CREATE TABLE `;`.““ (`.` INT); CREATE TABLE `;`.`$(ls)` (`.` INT);
The REPLACE [INTO] syntax allows us to INSERT a row into a table, except that if a UNIQUE KEY (including PRIMARY KEY) violation occurs, the old row is deleted prior to the new INSERT, hence no violation. Sounds very attractive, and has a nice syntax as well: the same syntax as a normal INSERT INTO’s. [...]
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.
It is a known trick to use a session variable for dynamically counting/sequencing rows. The way to go is to SET a variable to zero, then use arithmetic within assignment to increment its value for each row in the SELECTed rows.
But can it be achieved with one query only? That’s more of a problem… I’ll provide such a solution, albeit not a pretty one.