Monotonic functions, SQL and MySQL

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) < f(y).

So, if we follow values in some order, we say that f is monotonic increasing if f‘s value never decreases (it either increases or stays the same), and we say that f is strictly increasing if f‘s value is always changes “upwards”.

Monotonic functions play an important role in SQL. To discuss monotonic functions in SQL we must first determine what the order is, and then, what the function is.

Well, they both change according to our point of view. Let’s look at some examples. Take a look at the following table:

CREATE TABLE `log` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `error_level` tinyint(4) DEFAULT NULL,
 `subject` varchar(32) DEFAULT NULL,
 `description` varchar(255) DEFAULT NULL,
 PRIMARY KEY (`id`)
)

In the above log table, log entries are added with id and ts getting automatically evaluated. Assuming no dirty hacks occur, we can expect that ts in monotonic by order of id. That is, as id increases, so does ts. Is is possible that we get the same ts for a few rows (it is not unique), but once it increases, it never decreases again.

Why is this interesting?

Because it simplifies common problems.

For example, it simplifies a search for a given ts value, when no index exists on the ts column. If we were to look for a log entry from ‘2009-02-07 11:58:00’ by simple SELECT, we would have to use a full table scan. But, by knowing that ts is monotonic, we can also use binary search on id.

As another example, it simplifies the task of purging all rows up to last midnight. Instead of issuing “DELETE FROM log WHERE ts < DATE(NOW())”, thus using, again, full table scan plus locking all rows (depending on storage engine), we can use other methods:

• We can detect the id for the first row which holds the condition using binary search, then “DELETE FROM log WHERE id < ###”
• Or we can slowly work our way in ascending id order, issuing something like “DELETE FROM log WHERE ts < DATE(NOW()) ORDER BY id ASC LIMIT 1000”, and stop once the ROW_COUNT() is less than 1000. We know we need not advance any further, without needing to compute anything. We thus block less, while retaining correctness of our operation.

Monotonic functions in MySQL

When we iterate InnoDB tables (as in full table scan), we know that rows are iterated in ascending PRIMARY KEY order [¹]. So the PRIMARY KEY dictates the order by which monotonic functions are evaluated.

With MyISAM, rows are iterated according to internal storage order. It has nothing to do with PRIMARY KEYs (though depending on concurrent_insert this can be somewhat controlled). It also has nothing to do with chronological order. Newer rows may capture space held by older rows.

But MyISAM allows for ALTER TABLE … ORDER BY … syntax, which allows us to do a one-time sort of the table. Assuming no writes shortly thereafter, a full table scan will iterate the rows according to specified order.

Monotonic functions and indexes

A column which is indexed dictates a monotonic function by index order.

Wait. Isn’t that obvious? Of course: if we index a column, then the index sorts by that column, and the column is ascending by the index order which is,… itself.

I call that trivial, but it does interest us: because, while mathematically there may be nothing significant here, we do care about this order when we have index scans. So, if we can force an index scan on our query, then we can anticipate the order by which rows are processed; we now have some order by which to evaluate monotonic functions.

OK, maybe I made it sound more complicated than it really is. Monotonic functions work well when the order by which they are monotonic is some indexed column(s). The AUTO_INCREMENT PRIMARY KEY we saw in the log example above, is perhaps the most trivial case.

While MySQL does not support function indexes, if the function we consider is monotonic, we still benefit from adding an index on the raw column.

Other examples of monotonic functions

So, where else can we find them? Timestamp columns are probably the most common (this post holds true until time travel to the past is introduced).

But also summaries: like a reporting table which lists down some ever-ascending value (the number of books ever sold in our store; trip mileage; hop counter; etc.).

I’ve seen many cases (though difficult to illustrate in this scope) when foreign key values are in ascending order. A very brief example is a 1-1 relation between two denormalized tables, where the tables ids do not necessarily have to match, but is always ascending).

And Baron’s wishlist for SQL can also benefit from monotonic functions.

Conclusion

When a monotonic function is present, it brings an added value to our schema and query design. It allows for less indexing; quicker operations. Look for these. I’ve only discussed increasing functions. Indeed, MySQL’s indexes are always increasing (they cannot be defined in decreasing order), but query simplifications work just as well for monotonic decreasing functions.

[¹] I’ve actually seen a different behavior on temporary InnoDB tables and on compressed InnoDB Plugin tables; I’ll write on this on another occasion.