Mysql – Efficient way to write query for multiple table which depends on column value of a table in thesql

join;MySQLperformancequery-performance

In the given schema, there is a table A having column A1,A2,A3,A4

There are three more tables i.e. B,C,D

If the value in A4 is b, i have to lookup table B. If the value in A4 is c, i have to lookup table C. If the value in A4 is d, i have to lookup table D.

Currently, we have implemented using switch case i.e.

select * from A
where
CASE
  WHEN SUBSTRING(A4,4,3) IN ( '023','009','011','013','015','017','019','021')
    THEN A3 NOT IN ( select Z from B) 
  WHEN SUBSTRING(A4,4,3) IN ( '006','024','028','031')
    THEN A3 NOT IN ( select Z from C) 
  WHEN SUBSTRING(A4,4,3) IN ( '004','025')
    THEN A3 NOT IN ( select Z from D) 
END

Is there any better and more efficient way to resolve this problem?

Best Answer

First of all, append a computed column, say A4Key, set to SUBSTRING(A4, 4, 3).

This column shall be kept up-to-date either by your application code or by triggers, such as:

CREATE TRIGGER ... BEFORE UPDATE ...
...
    SET NEW.A4Key = SUBSTRING(NEW.A4, 4, 3)
...

CREATE TRIGGER ... BEFORE INSERT ...
...
    SET NEW.A4Key = SUBSTRING(NEW.A4, 4, 3)
...

first solution

Then, rewrite your query to:

SELECT * FROM A
WHERE A4Key IN('023', '009', '011', '013', '015', '017', '019', '021')
  AND A3 NOT IN(SELECT Z FROM B)
-- -------
UNION ALL
-- -------
SELECT * FROM A
WHERE A4Key IN('006', '024', '028', '031')
  AND A3 NOT IN(SELECT Z FROM C)
-- -------
UNION ALL
-- -------
SELECT * FROM A
WHERE A4Key IN('004', '025')
  AND A3 NOT IN(SELECT Z FROM D)

Now, evaluate your query plans to devise the best indexes.

second solution

Create a table such as:

CREATE TABLE A4Exceptions(
  A4Key VARCHAR(3),
  Z     `whichever_type_of_Z`
)

With initial data set by:

INSERT INTO A4Exceptions SELECT '023', Z FROM B
INSERT INTO A4Exceptions SELECT '009', Z FROM B
...
INSERT INTO A4Exceptions SELECT '006', Z FROM C
...

And keep it up-to-date with the changing (?) data from B, C and D.

Done that, now you may, with blazing performance (after creating some indexes):

SELECT *
FROM A
WHERE NOT EXISTS(
  SELECT *
  FROM A4Exceptions exc
  WHERE exc.A4Key = A.A4Key
    AND ext.Z     = A.A3
)
Related Question