How to create index for select query in SQL?-CodePudding

I have a query as below, and need to create a index for the XYZ field, can we create index in the select statement:

SELECT ABC,
CASE
WHEN B IS NULL AND C IS NOT NULL THEN CONCAT(C,'/',D)
WHEN B IS NOT NULL AND C IS NULL THEN CONCAT(B,'/',D)
WHEN C IS NOT NULL AND C IS NOT NULL THEN CONCAT(B,'/',C,'/',D)
ELSE -1
END AS XYZ,
FROM TABLE_NAME
WHERE ABC=123

Since this XYZ field is not available in 'from table', we unable to create index as usual in create table. Please help with this.

Thanks

CodePudding user response：

You could alter your table definition to include XYZ as a generated computed column. Then, add an index on that computed column.

ALTER TABLE TABLE_NAME ADD COLUMN XYZ VARCHAR(50) GENERATED ALWAYS AS
CASE
    WHEN B IS NULL AND C IS NOT NULL THEN CONCAT(C, '/', D)
    WHEN B IS NOT NULL AND C IS NULL THEN CONCAT(B, '/', D)
    WHEN C IS NOT NULL AND C IS NOT NULL THEN CONCAT(B, '/', C, '/', D)
    ELSE '-1' END
STORED;

CREATE INDEX idx_xyz ON TABLE_NAME (XYZ);

CodePudding user response：

Usually, one looks at the WHERE clause first to decide on an INDEX. In this case, INDEX(ABC) is very likely to be helpful.