NOP optimises Group By?

[Peter Griffioen]

G'day, I've finally got this simple but massive query running in reasonable time but I don't know why. It only used two tables, but the main table is 22 million rows. The main contains a PointId (= map coordinate as int), Species number (Int) and Probability of Presence (tinyint), all indexed. The second is a histogram lookup for each of the 523 species probability of presence (1 to 100 where present) providing a Weight (46,000 rows).

The basic query takes the average Weight across all species at each point id, for 523 species:

SELECT sppmodelvalues.PointId, Sum((100-[PercentExtent]))/523 AS Weight, Count(sppmodelvalues.SppId) AS SppCount
FROM sppmodelvalues INNER JOIN spphistograms ON (sppmodelvalues.SppId = spphistograms.SppId) AND (sppmodelvalues.Prob = spphistograms.Prob)
GROUP BY sppmodelvalues.PointId;


This runs VERY slowly, not completing in 24 hours!. However if I add the following, apparently useless restriction, 'WHERE (((sppmodelvalues.SppId)>0))' the query runs in 5 minutes! Note that no SppId are <= 0.

i.e. Fast Quesy

SELECT sppmodelvalues.PointId, Sum((100-[PercentExtent]))/523 AS Weight, Count(sppmodelvalues.SppId) AS SppCount
FROM sppmodelvalues INNER JOIN spphistograms ON (sppmodelvalues.SppId = spphistograms.SppId) AND (sppmodelvalues.Prob = spphistograms.Prob)
WHERE (((sppmodelvalues.SppId)>0))
GROUP BY sppmodelvalues.PointId;

My .Net software asks for a map row at a time rather than all results at once:

Use SppModels;
SELECT sppmodelvalues.PointId, Sum((100-PercentExtent))/523 AS Weight, Count(sppmodelvalues.SppId) AS SppCount FROM sppmodelvalues
INNER JOIN spphistograms ON (sppmodelvalues.SppId = spphistograms.SppId) AND (sppmodelvalues.Prob = spphistograms.Prob)
WHERE (sppmodelvalues.PointId >= 351865 AND sppmodelvalues.PointId <= 353493) AND (((sppmodelvalues.SppId)>0))
GROUP BY sppmodelvalues.PointId;

My question is why does this No-op 'WHERE (((sppmodelvalues.SppId)>0)) ' make a difference?

I've got MYSQL 5.5.3 running on a lovely 8-core workstation with Win 7-64 and 100GB of RAM!

Thanks in advance, Peter