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);
@lad2025 is correct that MySQL does not support function-based indexes (like PostgreSQL does), but MySQL 5.7 introduced a feature for virtual generated columns based on expressions, and then you can create an index on a virtual column.
ALTER TABLE tb_kse ADD COLUMN either_no VARCHAR(10) AS (IFNULL(ss_no, id_no));
CREATE INDEX IDX_KSE_NO_01 ON tb_kse(either_no);
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 = ?;
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 = ?;
(ss_no, id_no) -- such will work for the first SELECT and work much more efficiently for the second.
ORDER BY ISNULL(...)? OrSELECT ISNULL...? Or shrinking the index? Or not displaying those withNULL?