Optimizing JOIN to gain speed

[stefaan.lhermitte]

Dear mysql-ians,

I am performing a query that takes ages before showing a result. I
suppose it is very time consuming because of the JOIN I perform. My
question therefore is if you have any suggestions to optimize my query?

What I want to do is divide a value of a cell (NDVI) by the median of
its neighbours based on geographical coordinates (refgeo).
The selection script is as follows:

SELECT
NDVI.id,
NDVI.NDVI_99_24*IF(B.B_99_24<30,1,NULL)/MEDIAN(refNDVI.NDVI_99_24*IF(refB.B_99_24<30,1,NUL L)),
NDVI.NDVI_99_25*IF(B.B_99_25<30,1,NULL)/MEDIAN(refNDVI.NDVI_99_25*IF(refB.B_99_25<30,1,NUL L)),
NDVI.NDVI_99_26*IF(B.B_99_26<30,1,NULL)/MEDIAN(refNDVI.NDVI_99_26*IF(refB.B_99_26<30,1,NUL L)),
....,
NDVI.NDVI_99_170*IF(B.B_99_170<30,1,NULL)/MEDIAN(refNDVI.NDVI_99_170*IF(refB.B_99_170<30,1,N ULL))

FROM vgt.NDVI AS NDVI
INNER JOIN vgt.B AS B
USING (id)
INNER JOIN vgt.v_ecoclim AS eco
USING (id)
INNER JOIN vgt.v_vcf2001 AS vcf
USING (id)
,
(vgt.meta_gsc AS refmgsc
INNER JOIN vgt.gsc AS refgsc
ON refgsc.gsc_XX = refmgsc.id)
INNER JOIN vgt.geo AS refgeo
ON refgeo.id = refgsc.id
INNER JOIN vgt.B AS refB
ON refgeo.id = refB.id
INNER JOIN vgt.v_ecoclim AS refeco
ON refgeo.id = refeco.id
INNER JOIN vgt.v_vcf2001 AS refvcf
ON refgeo.id = refvcf.id
INNER JOIN (vgt.gba AS refgba
INNER JOIN vgt.meta_gba AS refmgba
ON refgba.gba_XX = refmgba.id)
ON refgeo.id=refgba.id
INNER JOIN vgt.NDVI AS refNDVI
ON refgeo.id = refNDVI.id
WHERE NDVI.id= 1133568
AND refgeo.X_coord > 1288
AND refgeo.X_coord < 1328
AND refgeo.Y_coord > 659
AND refgeo.Y_coord < 699
AND refmgba.burnt=0
AND refmgsc.burnt=0
AND refeco.v_landcov=eco.v_landcov
AND refeco.v_lowreb=eco.v_lowreb
AND refvcf.vcf_bare < vcf.vcf_bare+20
AND refvcf.vcf_bare > vcf.vcf_bare-20
AND refvcf.vcf_tree < vcf.vcf_tree+20
AND refvcf.vcf_tree > vcf.vcf_tree-20
AND refvcf.vcf_herb < vcf.vcf_herb+20
AND refvcf.vcf_herb > vcf.vcf_herb-20
GROUP BY refmgba.burnt

I have thus several main-tables that have identical rows (and are
linked by id in a 1to1-relatioship ) and I have 2 sub-tables in a
manyto1-relationship. I have put it in a scheme below (view using
Courier font for correct spacing).

The main tables are: NDVI, B, eco, vcf, refgsc, refgeo, refeco, refgba,
refB, refvcf, refNDVI.
The 2 subtables: refmgba, refmgsc.

Is my query correct? Do you have any solutions to make my query more
efficient?
I've studied the mysql-manual, but I don't see answers on the moment

Thanks in advance,
Stef

Schematic overview tables:

[=============] [=============] [============]
[T_Main: gsc ] [T_Main: gba ] [T_Main:NDVI ] O
[=============] [=============] [============] T
[ ID ]<--->[ ID ]<->[ ID ]<->H
|-->[ gsc_XX(many)] |->[ gba_XX(many)] [ NDVI_99_24 ] E
| [ Others... ] | [ Others... ] [ NDVI_99_25 ] R
| [=============] | [=============] [ Others... ] S
| | [============]
| |
| |
| [=============] | [=============]
| [T: refmgsc ] | [T: refmgba ]
| [=============] | [=============]
|--> [ ID(1) ] |--> [ ID(1) ]
[ BURNT ] [ BURNT ]
[ Others... ] [ Others... ]
[=============] [=============]

[noone]

st***************@agr.kuleuven.ac.be wrote:

Dear mysql-ians, I am performing a query that takes ages before showing a result. I
suppose it is very time consuming because of the JOIN I perform. My
question therefore is if you have any suggestions to optimize my query? What I want to do is divide a value of a cell (NDVI) by the median of
its neighbours based on geographical coordinates (refgeo).
The selection script is as follows: SELECT
NDVI.id,
NDVI.NDVI_99_24*IF(B.B_99_24<30,1,NULL)/MEDIAN(refNDVI.NDVI_99_24*IF(refB.B_99_24<30,1,NUL L)), NDVI.NDVI_99_25*IF(B.B_99_25<30,1,NULL)/MEDIAN(refNDVI.NDVI_99_25*IF(refB.B_99_25<30,1,NUL L)), NDVI.NDVI_99_26*IF(B.B_99_26<30,1,NULL)/MEDIAN(refNDVI.NDVI_99_26*IF(refB.B_99_26<30,1,NUL L)), ....,
NDVI.NDVI_99_170*IF(B.B_99_170<30,1,NULL)/MEDIAN(refNDVI.NDVI_99_170*IF(refB.B_99_170<30,1,N ULL))
FROM vgt.NDVI AS NDVI
INNER JOIN vgt.B AS B
USING (id)
INNER JOIN vgt.v_ecoclim AS eco
USING (id)
INNER JOIN vgt.v_vcf2001 AS vcf
USING (id)
,
(vgt.meta_gsc AS refmgsc
INNER JOIN vgt.gsc AS refgsc
ON refgsc.gsc_XX = refmgsc.id)
INNER JOIN vgt.geo AS refgeo
ON refgeo.id = refgsc.id
INNER JOIN vgt.B AS refB
ON refgeo.id = refB.id
INNER JOIN vgt.v_ecoclim AS refeco
ON refgeo.id = refeco.id
INNER JOIN vgt.v_vcf2001 AS refvcf
ON refgeo.id = refvcf.id
INNER JOIN (vgt.gba AS refgba
INNER JOIN vgt.meta_gba AS refmgba
ON refgba.gba_XX = refmgba.id)
ON refgeo.id=refgba.id
INNER JOIN vgt.NDVI AS refNDVI
ON refgeo.id = refNDVI.id
WHERE NDVI.id= 1133568
AND refgeo.X_coord > 1288
AND refgeo.X_coord < 1328
AND refgeo.Y_coord > 659
AND refgeo.Y_coord < 699
AND refmgba.burnt=0
AND refmgsc.burnt=0
AND refeco.v_landcov=eco.v_landcov
AND refeco.v_lowreb=eco.v_lowreb
AND refvcf.vcf_bare < vcf.vcf_bare+20
AND refvcf.vcf_bare > vcf.vcf_bare-20
AND refvcf.vcf_tree < vcf.vcf_tree+20
AND refvcf.vcf_tree > vcf.vcf_tree-20
AND refvcf.vcf_herb < vcf.vcf_herb+20
AND refvcf.vcf_herb > vcf.vcf_herb-20
GROUP BY refmgba.burnt I have thus several main-tables that have identical rows (and are
linked by id in a 1to1-relatioship ) and I have 2 sub-tables in a
manyto1-relationship. I have put it in a scheme below (view using
Courier font for correct spacing). The main tables are: NDVI, B, eco, vcf, refgsc, refgeo, refeco, refgba,
refB, refvcf, refNDVI.
The 2 subtables: refmgba, refmgsc. Is my query correct? Do you have any solutions to make my query more
efficient?
I've studied the mysql-manual, but I don't see answers on the moment Thanks in advance,
Stef Schematic overview tables: [=============] [=============] [============]
[T_Main: gsc ] [T_Main: gba ] [T_Main:NDVI ] O
[=============] [=============] [============] T
[ ID ]<--->[ ID ]<->[ ID ]<->H
|-->[ gsc_XX(many)] |->[ gba_XX(many)] [ NDVI_99_24 ] E
| [ Others... ] | [ Others... ] [ NDVI_99_25 ] R
| [=============] | [=============] [ Others... ] S
| | [============]
| |
| |
| [=============] | [=============]
| [T: refmgsc ] | [T: refmgba ]
| [=============] | [=============]
|--> [ ID(1) ] |--> [ ID(1) ]
[ BURNT ] [ BURNT ]
[ Others... ] [ Others... ]
[=============] [=============]

make sure you have indexes that support your where-clause.