Search code examples
mysqlsqlindexingifnull

using ifnull with index creation in mysql

I am trying to create an index in MySQL whereas the query will first check what column is not null. After checking, it will create the index on the column that is not null. However, I am not successful in creating this and it says I have an error, can someone help me? please see my code below

create index IDX_KSE_NO_01 on tb_kse(ifnull(ss_no, id_no);
Solution

  • MySQL does not support function-based index. You should create normal index:

    create index IDX_KSE_NO_01 on tb_kse(ss_no);
create index IDX_KSE_NO_02 on tb_kse(id_no);

    And rewrite your query (OR-Expansion):

    SELECT *
FROM tb_kse WHERE ss_no = ?
UNION 
SELECT *
FROM tb_kse
WHERE ss_no IS NULL AND id_no = ?;

    DBFiddle Demo

    Another way is to create generated column and create index on top of it:

    CREATE TABLE tb_kse(id_no INT, ss_no INT,
       gen_col INT GENERATED ALWAYS AS (ifnull(ss_no, id_no)) STORED);

create index IDX_KSE_NO_01 on tb_kse(gen_col);

SELECT *
FROM tb_kse
WHERE gen_col = ?;

    DBFiddle Demo 2