on 12-12-2007 11:02 PM
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.
Hi,
from optimizer point of view it looks good now.
Perhaps you could increase your CACHE_SIZE to reduce I/O.
But this will only speed up the execution if the tables are already read by other operations.
Kind regards
Holger
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
for me it looks like the transition
C.CLIENTE_ID=CGR.ID_CLIENTE
is not well supported by your key or index definition.
Please try if a single index on CGR.ID_CLIENTE speed up your query.
Kind regards
Holger
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
i have created a single index in CGR.ID_CLIENTE (it is part of the, two columns, primary key), the stats seem to be better and the time it is better but i think it is possible it have any other problem because it is slow yet.
new explain:
C UQ_CODIGO_INDEX RANGE CONDITION FOR INDEX 9631
CODIGO_CLIENTE (USED INDEX COLUMN)
CGR IDCLIENTE_INDEX JOIN VIA INDEXED COLUMN 1407
ID_CLIENTE (USED INDEX COLUMN)
CG GRUPO_ID JOIN VIA KEY COLUMN 1
TABLE HASHED
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 33352
new explain sequence:
TABLE No. 1
C
INV RANGE FETCH
strat 0.185E+00
pages searc 0.179E+04
all_pages 9631
whole_IO_pa 1791
recs_per_p 42
rec_len 481
TABLE No. 2
D
KEY RANGE FETCH
strat 0.100E+01
pages searc 0.746E+04
all_pages 7469
whole_IO_pa 7469
recs_per_p 49
rec_len 484
TABLE No. 3
T
KEY RANGE FETCH
strat 0.100E+01
pages searc 0.456E+04
all_pages 4562
whole_IO_pa 4562
recs_per_p 94
rec_len 52
TABLE No. 4
CGR
KEY RANGE FETCH
strat 0.100E+01
pages searc 0.140E+04
all_pages 1407
whole_IO_pa 1407
recs_per_p 288
rec_len 26
TABLE No. 5
CG
KEY RANGE FETCH
strat 0.100E+01
pages searc 0.100E+01
all_pages 1
whole_IO_pa 1
recs_per_p 108
rec_len 148
| FROM 1 2 3 4 5
TO
1 -- SK SK SK ><
2 MJ -- = = =
3 MJ MJ -- MJ MJ
4 MJ = = -- MJ
5 ?? ?? ?? SK --
mt_cnt : 8
TO via multiplier T F J I Used tables
4 FIRST KEY 0.240E+04 1 1 0 0 [ 5 ]
4 KEY 0.100E+01 2 2 0 0 [ 1 5 ]
4 INDEXCOLUMNS 0.100E+01 1 1 2 1 [ 1 ]
3 INDEXCOLUMNS 0.100E+01 1 2 3 1 [ 1 ]
3 INDEX PART 0.216E+06 0 1 4 3 [ ]
3 UNIQUE INDE 0.100E+01 1 3 4 3 [ 1 ]
2 INDEX PART 0.100E+01 1 1 6 5 [ 1 ]
2 INDEXCOLUMNS 0.100E+01 1 2 6 1 [ 1 ]
TO via multiplier costs ji mi
1 SINGLE KEY 0.100E01 0.263446E02 2 0
2 INDEX-FIELDS 0.100E01 0.141666E03 1 8 multiplejoin
3 UNIQUE INDE 0.100E01 0.104807E03 1 6 multiplejoin
4 KEY 0.100E01 0.102403E03 1 2 multiplejoin
5 SINGLE KEY 0.100E01 0.113513E03 4 0
JOIN SEQUENCE SEARCH = AUTOMATIC - LEVEL 9
< COSTVALUE : 0.137507E+05 [ 1 4 5 ]
> COSTVALUE : 0.434198E+05 [ 4 5 1 ]
> COSTVALUE : 0.429135E+05 [ 5 4 1 ]
BUFFER SIZE PER TASK : 131072 BYTES
TABLE 1 : 130835 BYTES (recs est. 75222)
TABLE 4 : 0 BYTES (recs est. 1)
TABLE 5 : 148 bytes (recs est. 1)
TABLE 2 : 2 BYTES (recs est. 1)
TABLE 3 : 87 bytes (recs est. 1)
-
anyway works much better thant before, maybe this is the real best speed of the query?
Hi,
would you mind posting the output of the explain sequence here?
It's hard to read but might give use more information.
Kind regards
Holger
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi,
this is the explain sequence output:
TABLE No. 1
C
INV RANGE FETCH
strat 0.185E+00
pages searc 0.179E+04
all_pages 9631
whole_IO_pa 1791
recs_per_p 42
rec_len 481
TABLE No. 2
D
KEY RANGE FETCH
strat 0.100E+01
pages searc 0.746E+04
all_pages 7469
whole_IO_pa 7469
recs_per_p 49
rec_len 484
TABLE No. 3
T
KEY RANGE FETCH
strat 0.100E+01
pages searc 0.456E+04
all_pages 4562
whole_IO_pa 4562
recs_per_p 94
rec_len 52
TABLE No. 4
CGR
KEY RANGE FETCH
strat 0.100E+01
pages searc 0.140E+04
all_pages 1407
whole_IO_pa 1407
recs_per_p 288
rec_len 26
TABLE No. 5
CG
KEY RANGE FETCH
strat 0.100E+01
pages searc 0.100E+01
all_pages 1
whole_IO_pa 1
recs_per_p 108
rec_len 148
| FROM 1 2 3 4 5
TO
1 -- SK SK SK ><
2 MJ -- = = =
3 MJ MJ -- MJ MJ
4 = = = -- MJ
5 ?? ?? ?? SK --
mt_cnt : 7
TO via multiplier T F J I Used tables
4 FIRST KEY 0.240E+04 1 1 0 0 [ 5 ]
4 KEY 0.100E+01 2 2 0 0 [ 1 5 ]
3 INDEXCOLUMNS 0.100E+01 1 2 3 1 [ 1 ]
3 INDEX PART 0.216E+06 0 1 4 3 [ ]
3 UNIQUE INDE 0.100E+01 1 3 4 3 [ 1 ]
2 INDEX PART 0.100E+01 1 1 6 5 [ 1 ]
2 INDEXCOLUMNS 0.100E+01 1 2 6 1 [ 1 ]
TO via multiplier costs ji mi
1 SINGLE KEY 0.100E01 0.263446E02 2 0
2 INDEX-FIELDS 0.100E01 0.141666E03 1 7 multiplejoin
3 UNIQUE INDE 0.100E01 0.104807E03 1 5 multiplejoin
4 EQUAL FIELD 0.100E01 0.703612E06 1 0
5 SINGLE KEY 0.100E01 0.113513E03 4 0
JOIN SEQUENCE SEARCH = AUTOMATIC - LEVEL 9
< COSTVALUE : 0.105857E+09 [ 1 4 5 ]
< COSTVALUE : 0.189001E+07 [ 1 5 4 ]
< COSTVALUE : 0.735207E+05 [ 4 1 5 ]
< COSTVALUE : 0.434198E+05 [ 4 5 1 ]
< COSTVALUE : 0.429135E+05 [ 5 4 1 ]
BUFFER SIZE PER TASK : 131072 BYTES
TABLE 5 : 26420 BYTES (recs est. 108)
TABLE 4 : 103229 BYTES (recs est. 2402)
TABLE 1 : 481 bytes (recs est. 1)
TABLE 2 : 855 BYTES (recs est. 1)
TABLE 3 : 87 bytes (recs est. 1)
thanks for help, if this is the speed i can get, i think i should denormalize, should not?
User | Count |
---|---|
84 | |
25 | |
12 | |
9 | |
6 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.