I have a table with a few columns, two of which are integers, one of which can be NULL in which case the other integer is to be used in my query.

CREATE TABLE my_table (col1 INTEGER NOT NULL, col2 INTEGER);

If `col2` is NULL, then use `col1`. As is, there is how my query looks like:

SELECT MIN(IF(col2, col2, col1)) AS smallest FROM my_table LIMIT 1;

In other words, I need to get the row with the smallest integer, but if one column is NULL, I want to use the other column value.

On a very small sample, the query is still very fast, but the real version is not unlikely to have thousands of rows and I am thinking that a MIN() plus an IF() are going to slow things down quite a bit, even though I only need the first result (hence the LIMIT 1).

Since MySQL version 5.7.6, it is possible to create calculated columns in a table. This is done using the AS keyword. In our case we want to add a third column as follow:

CREATE TABLE my_table (
    col1 INTEGER NOT NULL,
    col2 INTEGER,
    col3 AS IF(col2, col2, col1)
);

With that new column, it is actually possible to create an index on that calculated column. In other words, we can add:

CREATE TABLE my_table (
    col1 INTEGER NOT NULL,
    col2 INTEGER,
    col3 AS IF(col2, col2, col1),
    INDEX my_table_ordered USING BTREE (col3) COMMENT 'that works!'
);

Now our SELECT becomes:

SELECT col3 AS smallest FROM my_table ORDER BY col3 LIMIT 1