slow query,it is this normal?

32 Views

Hello,

i am getting information from 4 tables with a join the tables are:

-CLIENTES, 404678 records

-DIRECCION, 366777 records

-TELEFONO, 432637 records

-CLIENTE_GRUPO_RELACION, 405976

i am doing this query:

SELECT C.CODIGO_CLIENTE, C.NOMBRE, C.APELLIDO1, C.APELLIDO2, C.NUMERO_TARJETA, T.NUMERO_TELEFONO, D.CODPOSTAL, D.PROVINCIA, D.POBLACION, D.TIPO_VIA, D.NOMBREVIA, D.NUMERO, D.PISO, CG.NOMBRE AS NOMBRE_GRUPO, C.EMAIL FROM SKUDA.CLIENTE AS C LEFT JOIN SKUDA.DIRECCION AS D ON C.CLIENTE_ID=D.ID_CODIGO_ORIGEN AND D.TIPO_ORIGEN='CLIENTE' LEFT JOIN SKUDA.TELEFONO AS T ON C.CLIENTE_ID=T.ID_CODIGO_ORIGEN AND T.TELEFONO_PRINCIPAL=TRUE AND T.TIPO_ORIGEN='CLIENTE' LEFT JOIN SKUDA.CLIENTE_GRUPO_RELACION AS CGR ON C.CLIENTE_ID=CGR.ID_CLIENTE JOIN SKUDA.CLIENTE_GRUPOS AS CG ON CGR.ID_GRUPO=CG.GRUPO_ID AND CGR.PRIMARIO=TRUE WHERE C.CODIGO_CLIENTE LIKE '30%'

it takes 18 seconds and gives me 63812 records. i get this explain output:

CG TABLE SCAN 1

CGR ID_GRUPO JOIN VIA KEY RANGE 1407

C CLIENTE_ID JOIN VIA KEY COLUMN 9631

D CODIGO_ORIGEN_INDEX JOIN VIA MULTIPLE INDEXED COLUMNS 7469

ID_CODIGO_ORIGEN (USED INDEX COLUMN)

TIPO_ORIGEN (USED INDEX COLUMN)

T UQ_ID_PRINCIPAL_TIPO JOIN VIA MULTIPLE INDEXED COLUMNS 4562

TELEFONO_PRINCIPAL (USED INDEX COLUMN)

ID_CODIGO_ORIGEN (USED INDEX COLUMN)

TIPO_ORIGEN (USED INDEX COLUMN)

NO TEMPORARY RESULTS CREATED

RESULT IS COPIED , COSTVALUE IS 148503

and this explain join output:

CG 108 1 1 2.91891891891892 108 1

JOIN VIA MORE THAN ONE FIELD CGR 405216 2402 1 7011.24324324324 259416 7912.99324324324

JOIN VIA SINGLE KEY C 75222 1 1 28824 259416 42913.5646718147

JOIN VIA MORE THAN ONE FIELD D 365981 1 1 43236 259416 93032.0299779371

JOIN VIA MORE THAN ONE FIELD T 428828 1 1 51883.2 259416 148502.898063043

i have updated stadistics but i think this it is too long? any visible problem with my query? i am not sql expert but i have this other query joins 2 tables with 55000 and 20000 records (much less i know but too much difference):

SELECT MO.CODIGO_INTERNO, MO.CODIGO_PROVEEDOR, MO.COLOR, TA.PRECIO, MO.NOMBRE FROM SKUDA.MODELO AS MO LEFT JOIN SKUDA.TARIFA_MODELO_PRECIO AS TA ON MO.MODELO_ID=TA.ID_MODELO WHERE TA.ID_TARIFA=3 AND MO.CODIGO_INTERNO LIKE '02%'

and this give me this explain:

MO UQ_CODIGO_OTEROS_INDEX RANGE CONDITION FOR INDEX 1444

CODIGO_OTEROS (USED INDEX COLUMN)

TA JOIN VIA MULTIPLE KEY COLUMNS 563

ID_MODELO (USED KEY COLUMN)

ID_TARIFA (USED KEY COLUMN)

NO TEMPORARY RESULTS CREATED

RESULT IS COPIED , COSTVALUE IS 661

this is normal behaviour or i am doing something wrong, all the columns involved in the join or select query are indexed.