MySQL Forums
Forum List  »  Optimizer & Parser

NOP optimises Group By?
Posted by: Peter Griffioen
Date: September 05, 2010 08:16PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
NOP optimises Group By?
3947
September 05, 2010 08:16PM
1361
September 11, 2010 01:22PM
1586
September 12, 2010 11:09PM
1412
September 13, 2010 10:56AM
1430
September 13, 2010 08:15PM
1355
September 13, 2010 10:46PM


Sorry, you can't reply to this topic. It has been closed.
This forum is currently read only. You can not log in or make any changes. This is a temporary situation.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.