stefaan.lhermitte@agr.kuleuven.ac.be wrote:
> SELECT vgt.obs,pix.NDVI/AVG(vgt.NDVI)
....
> GROUP BY vgt.obs;

This is a bit odd, because you are not grouping by pix.NDVI. In some
RDBMS implementations, it is mandatory to group by all columns mentioned
in the select-list that are not used inside aggregate functions. MySQL
permits this. However, the value it returns for pix.NDVI will be some
arbitrarily chosen value in the group. Though in this case I assume you
restrict the pix table sufficiently, so it doesn't cause any ambiguity.

> The EXPLAIN command gives me the following result:
> table type possible_keys key len ref
> rows Extra
> ----- ------ -------------------------- -------- -- --------------
> ----- --------------------------------------------
> eco ref PRIMARY,v_landcov,v_lowreb v_lowreb 3 const
> 17021 Using where; Using temporary; Using filesort
> geo eq_ref PRIMARY,X_coord,Y_coord PRIMARY 3 eco.id 1
> Using where
> gsc eq_ref PRIMARY,gsc2000_XX PRIMARY 3 geo.id 1
>
> mgsc eq_ref PRIMARY,burnt PRIMARY 3 gsc.gsc2000_XX 1
> Using where
> vgt ref id,obs id 4 geo.id 1
> Using where
> pix ref id,obs id 4 const 156
> Using where
> gba eq_ref PRIMARY,gba2000_XX PRIMARY 3 geo.id 1
>
> mgba eq_ref PRIMARY,burnt PRIMARY 3 gba.gba2000_XX 1
> Using where

The temporary table & filesort mentioned in the line for eco is probably
the culprit. These are on-disk data operations, which is very slow
compared to in-memory operations. The GROUP BY is probably requiring
the temp table, because it isn't using the index on vgt.obs. You could
try using "FORCE INDEX (obs)" to make it use that index. But I'm not
sure that this would relieve the filesort, and it would also prevent use
of the vg.id index.

> Does anyone has a suggestion to make it faster?

MySQL 5 _might_ deal with this better. In some circumstances, MySQL 5
can use more than one index per table. MySQL 4.1 and earlier have a
limitation of one index per table in a given query. MySQL 5 also has
some improvements to the filesort algorithm.

You could try increasing your key_buffer_size server parameter, so that
it's large enough to contain all the indexes used (according to the
EXPLAIN report). Also, preload the indexes.

See
http://dev.mysql.com/doc/refman/5.0/...variables.html
http://dev.mysql.com/doc/refman/5.0/...reloading.html

Regards,
Bill K.