CODEWITHSUNDEEP
mysqlsqljoinleft-join
Suanbit
Suanbit

Reputation: 491

Index on Left join in MySQL

I'm new to Mysql and im trying to do a left join between 2 tables but it takes ages to do it.

I think I might me missing something in my noob query. I don't know how indexes work very well but I think there have to be indexes for every join column.

Firstly, I create the index for the joinning columns (MANDT, KUNNR, POSNR, MODAT), as well as the columns that are going to be joined to the other table.

CREATE INDEX aux03_financiero_idx ON aux03_financiero (ESTAD, TIPPTO, MANDT,  KUNNR, POSNR, MODAT, DPTOCODE, SW_RRHH);

I also create the index on the table that I want to have all the columns.

CREATE INDEX aux71_idx ON aux71 (MANDT,KUNNR, POSNR, MODAT);

After creating the indexes on the joinning columns I run the query, but never ends.

Each table contain aprox. 3M rows, so it's not a big table. Mysql should be able to do this with out any probs. There arent't any foreign keys or primary keys created, but indexes should do it I think.

CREATE TABLE FINANCIERO71 as select 
        A.MANDT,
        A.KUNNR,
        A.MODAT,
        A.POSNR,
        A.MODAT_R,
        A.MOV_TYPE,
        B.ESTAD,
        A.ORIG as hotel_ori,
        A.DESt as hotel_des,
        case when A.ORIG!='' then substring(A.ORIG,1,2)
            else substring(B.DPTOCODE,1,2) end as pais_origen,
        B.TIPPTO,
        B.DPTOCODE,
        B.SW_RRHH,
        A.PUNTOS,
        B.PUNTOS2,
        A.CANC,
        A.DATUM,
        A.UZEIT,
        A.NUMMOV,
        A.NUMMOV_ORIG,
        A.FECHA_CANC, 
        A.FECHA_CADUC,
        A.modat_mes,
        A.modat_ano,
        A.prombasic,
        substring(A.FECHA_CADUC,1,6) as modat_caduc_mes,
        substring(A.FECHA_CANC,1,6) as modat_cancel_mes,
        A.MarcaH
From aux71 as A
left join aux03_financiero as B
    on A.MANDT = B.MANDT 
    AND A.KUNNR = B.KUNNR
    AND A.MODAT = B.MODAT 
    AND A.POSNR = B.POSNR;

Would really appreciate your help. If you need any more info please just tell me.

Upvotes: 0

Views: 502

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

The index that you want is:

CREATE INDEX aux03_financiero_idx ON aux03_financiero (MANDT, KUNNR, MODAT, POSNR);

These are the columns used for joining and they need to be the first columns in the index. You can add additional columns as well after these columns, but this should significantly improve your performance.

Upvotes: 2

Related Questions