Valentin Grégoire Valentin Grégoire - 4 months ago 7
SQL Question

Performance on View in MySQL

I have created a view on a table like so:

CREATE OR REPLACE VIEW V_BLA AS
SELECT
CONVERT(UIT_DNR USING latin1) AS internNr,
UIT_SNR AS schuldnr,
TRIM((SELECT SCH_OMS FROM BUSCHU WHERE SCH_DNR = UIT_DNR AND SCH_VNR = UIT_SNR)) AS omschrijving,
CASE
WHEN (SELECT TABDATA FROM BDTABE WHERE TABNR = 9000 AND VOLGNR = 1 AND TABTAAL = 0) = 1 THEN -- ALGEMEEN BESTAND
(SELECT CONCAT(TRIM(SC_FNAAM), ' ', TRIM(SC_VNAAM)) FROM SDSCIN WHERE SC_NR = LEFT(UIT_SCHULDEISER, 4))
ELSE
(SELECT CONCAT(TRIM(SC_FNAAM), ' ', TRIM(SC_VNAAM)) FROM BUSCIN WHERE SC_NR = 100000 - UIT_SCHULDEISER)
END AS schuldeiser,
UIT_DATUM AS datum,
UIT_VLGNR AS volgnummer,
UIT_BEDRAG AS bedrag,
CONVERT(IF(
UIT_COBE_DAT = 0,
NULL,
CONCAT(
RIGHT(UIT_COBE_DAT, 4), '-',
IF(UIT_COBE_DAT < 10000000,
CONCAT(MID(UIT_COBE_DAT, 2, 2), '-0', LEFT(UIT_COBE_DAT, 1)),
CONCAT(MID(UIT_COBE_DAT, 3, 2), '-', LEFT(UIT_COBE_DAT, 2))
))), DATE) AS datumBetaallijst,
TRIM(UIT_MEDEDELING) AS mededeling
FROM BUUITG;


The problem is that the performance is really bad. It takes up to 10 seconds to generate output when I try to select some rows by internNr. I have created the necessary indices:


  • BDTABE: CREATE INDEX BDTABE_TABNR_VOLGNR ON BDTABE(TABNR, VOLGNR);

  • SDSCIN: CREATE INDEX SDSCIN_SC_NR ON SDSCIN(SC_NR);

  • BUSCIN: CREATE INDEX BUSCIN_SC_NR ON BUSCIN(SC_NR);



An extra index on UIT_DNR on BUUITG doesn't improve anything.

My explain plan when I do a select:

EXPLAIN SELECT * FROM V_BLA WHERE interNr = 'something';

+---+--------------------+------------+-----+------------------------+------------------------+----+-----------------------------------------------------------+--------+--------+-----------------------------------------------------+
| 1 | PRIMARY | <derived2> | ref | <auto_key0> | <auto_key0> | 22 | const | 10 | 100.00 | Using where |
+---+--------------------+------------+-----+------------------------+------------------------+----+-----------------------------------------------------------+--------+--------+-----------------------------------------------------+
| 2 | DERIVED | BUUITG | ALL | | | | | 208498 | 100.00 | |
| 6 | DEPENDENT SUBQUERY | BUSCIN | ref | BUSCIN_SC_NR | BUSCIN_SC_NR | 4 | func | 1 | 100.00 | Using index condition |
| 5 | DEPENDENT SUBQUERY | | | | | | | | | Impossible WHERE noticed after reading const tables |
| 4 | SUBQUERY | BDTABE | ref | BDTABE_TABNR_VOLGNR | BDTABE_TABNR_VOLGNR | 14 | const,const | 1 | 100.00 | Using where |
| 3 | DEPENDENT SUBQUERY | BUSCHU | ref | BUSCHU_SCH_VNR_SCH_DNR | BUSCHU_SCH_VNR_SCH_DNR | 12 | export_winsoc.BUUITG.UIT_SNR,export_winsoc.BUUITG.UIT_DNR | 1 | 100.00 | |
+---+--------------------+------------+-----+------------------------+------------------------+----+-----------------------------------------------------------+--------+--------+-----------------------------------------------------+

Answer

As already indicated in the comment, I would move the subqueries in the select list to the from clause, either as simple joins, or derived tables. I use left joins, but feel free to change them to inner joins based on your requirements and data. Pls also review the join conditions as well, since I had to guess in certain cases which fields are in which tables.

CREATE OR REPLACE VIEW V_BLA AS
SELECT
    CONVERT(UIT_DNR USING latin1) AS internNr,
    UIT_SNR AS schuldnr,
    TRIM(BUSCHU.SCH_OMS ) AS omschrijving,
    CASE
        WHEN BDTABE.TABDATA  = 1 THEN -- ALGEMEEN BESTAND
            CONCAT(TRIM(SDSCIN.SC_FNAAM), ' ', TRIM(SDSCIN.SC_VNAAM)) 
        ELSE
            CONCAT(TRIM(BUSCIN.SC_FNAAM), ' ', TRIM(BUSCIN.SC_VNAAM) 
    END AS schuldeiser,
    UIT_DATUM AS datum,
    UIT_VLGNR AS volgnummer,
    UIT_BEDRAG AS bedrag,
    CONVERT(IF(
        UIT_COBE_DAT = 0,
        NULL,
        CONCAT(
            RIGHT(UIT_COBE_DAT, 4), '-', 
            IF(UIT_COBE_DAT < 10000000,
                CONCAT(MID(UIT_COBE_DAT, 2, 2), '-0', LEFT(UIT_COBE_DAT, 1)),
                CONCAT(MID(UIT_COBE_DAT, 3, 2), '-', LEFT(UIT_COBE_DAT, 2))
            ))), DATE) AS datumBetaallijst,
    TRIM(UIT_MEDEDELING) AS mededeling
FROM BUUITG
LEFT JOIN BUSCHU ON BUSCHU.SCH_DNR=BUUITG.UIT_DNR AND BUSCHU.SCH_VNR=BUUITG.UIT_VNR
LEFT JOIN BDTABE ON BDTABE.TABNR = 9000 AND BDTABE.VOLGNR = 1 AND BDTABE.TABTAAL = 0 --did not find any common fields between the 2 tables
LEFT JOIN SDSCIN ON SDSCIN.SC_NR = LEFT(BUUITGUIT_SCHULDEISER, 4)
LEFT JOIN BUSCIN ON BUSCIN.SC_NR = 100000 - BUUITG.UIT_SCHULDEISER