The Linux Page

Can I create an INDEX to accelerate a SELECT MIN(IF(...)) in MySQL?

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
Syndicate content

SMS From Me Logo

SMS From Me

To automatically start one on one conversations with your online leads.

     

   

Terms of Site Index

Find the page/content you are looking for with our index.

  • RAM
    Random Access Memory. Everyone with a computer uses a lot of RAM to run their processes. This is true of any electronic device with some dynamic capabilities (like your cell phone, pocket calculator, watch with calculator, etc.)
  • installation
  • ping.fm
  • return
  • wireless