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 find [...]
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 table [...]
In mathematics, a monotonic function (or monotone function) is a function which preserves the given order. [Wikipedia]
To be more precise, a function f is monotonic increasing, if for every x ≤ y it holds that f(x) ≤ f(y). f is said to be strictly monotonic increasing is for every x < y it holds that f(x) [...]
Ever so often a query provides a “bad” execution plan. Adding a missing index can many times solve the problem. However, not everything can be solved with an index. I wish to highlight the point of having an implicit cast, which negates the use of an index on MySQL.
I see this happening a lot on [...]
A permutation is a change of places. Thus, ‘lolhe’ is a permuted ‘hello’ (commonly referred to as ’scrambled text’).
I wish to present an SQL solution for checking if two strings are permutations of the same text.
About permutations
So, if ‘lolhe’ is a permutation of ‘hello’, then ‘hello’ is a permutation of ‘lolhe’, as well; and both [...]
INSERT DELAYED may come in handy when using MyISAM tables. It may in particular be useful for log tables, where one is required to issue frequent INSERTs on one hand, but does not usually want or need to wait for DB response on the other hand.
It may even offer some performance boost, by aggregating such [...]
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 [...]
Time for another charting SQL query. I wish to present a single-query generated multi-line/area chart. I’ll walk through some of the steps towards making this happen. By the end of this post I’ll present some real-data charts, area charts and colored charts.
+———+———————————————————————————–+
| y_scale | chart |
+———+———————————————————————————–+
| 1 | ****———#######—————————————-*******——–###### |
| 0.88 | —-***—###——-##———————————–***——-***—##—— |
| 0.75 [...]
Following Baron Schwartz’ post: How to calculate a good InnoDB log file size, which shows how to make an estimate for the InnoDB log file size, and based on SQL: querying for status difference over time, I’ve written a query to run on MySQL 5.1, which, upon sampling 60 seconds of status, estimates the InnoDB [...]
The InnoDB plugin has a nice INFORMATION_SCHEMA concept: resetting tables. For example, the INNODB_CMP table lists information about compression operation. A similar table, INNODB_CMP_RESET, provides the same information, but resets the values. The latter can be used to measure, for example, number of compression operations over time.
I wish to present a SQL trick which does [...]