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
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);
Usually, one looks at the WHERE clause first to decide on an INDEX. In this case, INDEX(ABC) is very likely to be helpful.
(ABC)or by(ABC, B, C, D). Since this XYZ field is not available in 'from table', we unable to create index as usual in create table. You do NOT need by such index. Or you may add generated column using shown expression and index it... WHEN C IS NOT NULL AND C IS NOT NULL ..What's the reason for to check the column twice?CONCAT_WS('/', B, C, D).