Question

Hi!

What is the best indexes for this case? (Using MySQL)

SELECT 
    tcc.idCont,tcc.idCat,tcc.Priority, tC.idCatType
FROM tC
    JOIN tCC ON tcc.idCat=tc.idCat AND tCC.Init_Date < @pDate  AND (tCC.End_date >= @pDate OR tCC.End_date = 0) 
    JOIN tPCP ON tPCP.idSite = tC.idSite AND tPCP.idCont = tCC.idCont AND tPCP.Init_Date  < @pDate AND (tPCP.End_date>= @pDate OR tPCP.End_date= 0) 
    JOIN trFR ON trFR.idCont = tCC.idCont 
    JOIN trFT ON trFT.idUser= @pIdUser AND trFT.idFile = trFR.idFile AND trFT.idLang=@pidLang
WHERE tC.idCat = @pIdCat;

I think that I can use this index:

tC = KEY IDX_tC_1 (idCat,idSite);

tCC = KEY IDX_tCC_1 (idCat,idCont,Init_Date,End_date);

tPCP = KEY IDX_tPCP_1 (idSite,idCont,Init_Date,End_date);

trFR = KEY IDX_trFR _1 (idFile,idCont);

trFT = KEY IDX_trFR _1 (idUser,idFile,idLang);

The order of the colums is the optimal? For example, in trFR I don't know if is better put the "idCont" before "idFile" because is the foreign_key to his 'left_table', or switch it because "idFile" is the foreign_key to the 'right_table'.

Thanks!

Answer 1

Thanks! :)

This is the current 'explain':

id  select_type  table  type    possible_keys                 key                key_len    ref          rows    Extra
1   SIMPLE       tC     const   PRIMARY,FK_tcat_tsite         PRIMARY               8     const             1
1   SIMPLE       tCC    ref     UK_tcc,IDX_tcc_idConCat_init, IDX_tcc_idCat         8     const          1197    Using where
                                IDX_tcc_idCat,IDX_tcc_idCont,
                                IDX_tcc_idConCat
1   SIMPLE       tPCP   ref     IDX_tPCP_idCont,              IDX_tPCP_idCont       9     ecap.tCC.idContl  1    Using where
                                IDX_tPCP_idSite,
                                IDX_tPCP_idCont_idSite
1   SIMPLE       trFR   ref     PRIMARY,                      IDX_Fichero_Contenido 8     ecap.tPCP.idCont 10    Using where; Using index
                                IDX_idContenidoIdFormato,
                                IDX_Fichero_Contenido       
1   SIMPLE       trFT   eq_ref  PRIMARY,IDX_trFT_idUser,      PRIMARY              20     ecap.trFR.idFile  1    Using index
                                IDX_trFT_idLang

(Some redundant indexes in that tables :s (Simple-Column index with the same column of 1st column in a multi-column index) like IDX_tcc_idConCat -> IDX_tcc_idConCat_init (Same 1st and 2nd column, plus Date_init), etc... )

This is a "SHOW PROFILE CPU FOR QUERY 1;" result:

Status  Duration    CPU_user    CPU_system
starting    0.000028    0.000000    0.000000
checking query cache for query  0.000151    0.000000    0.000000
checking permissions    0.000005    0.000000    0.000000
checking permissions    0.000002    0.000000    0.000000
checking permissions    0.000002    0.000000    0.000000
checking permissions    0.000002    0.000000    0.000000
checking permissions    0.000009    0.000000    0.000000
Opening tables  0.000026    0.000000    0.000000
System lock 0.000007    0.000000    0.000000
Table lock  0.000011    0.000000    0.000000
init    0.000074    0.000000    0.000000
optimizing  0.000039    0.000000    0.000000
statistics  0.000291    0.000999    0.000000
preparing   0.000047    0.000000    0.000000
executing   0.000003    0.000000    0.000000
Sending data    1.331694    1.330798    0.000000
end 0.000012    0.000000    0.000000
query end   0.000004    0.000000    0.000000
freeing items   0.000061    0.000000    0.000000
logging slow query  0.000002    0.000000    0.000000
cleaning up 0.000005    0.000000    0.000000

Version: 5.1.56-log

Thanks, and enjoy your holidays! ;)