Re: NOP optimises Group By? -solved
Rick,
I have solved this while looking at supplying the data you requested. Its the old 'Having vs Where' mistake!
I found a bug in my software that breaks up the original SQL statement to request the map row by map row. The software allows you to prototype/design your sql map request in ACCESS and then translates sql statement to output a map. It does this as every request I get has slightly different requirements. If no WHERE clause was found in the statement, it would insert a HAVING clause if there is a 'GROUP BY' part, making the query really slow. If there was a 'WHERE' clause it augments that. It obvious it should always add a where clause regardless. Hence the 'where SppId > 0' made my translator not include a 'Having' clause. I feel really stupid for not realising that I was debugging on the wrong side of the translation!
Thus this is the really slow query:
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)
GROUP BY sppmodelvalues.PointId HAVING (sppmodelvalues.PointId >= 1 AND sppmodelvalues.PointId <= 1629);
which should be:
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 >= 1 AND sppmodelvalues.PointId <= 1629)
GROUP BY sppmodelvalues.PointId ;
Thanks again for your time and effort as the suggestions you made enlightened me to MySQL commands that are often not mentioned but really useful.
Cheers
(a sheepish) Peter Griffioen
ps. I'll leave this part of the reply to your suggestions in place in case you're interested or if you can suggest setting some buffers to other values.
--------------------------------------------
Foregone reply
--------------------------------------------
Thanks for your reply. Here's the data you requested.
Table Creates
============
CREATE TABLE `sppmodelvalues` (
`PointId` int(10) NOT NULL,
`SppId` smallint(5) NOT NULL,
`Prob` tinyint(3) NOT NULL,
PRIMARY KEY (`SppId`,`PointId`) USING BTREE,
KEY `Index_2` (`SppId`),
KEY `Index_3` (`Prob`),
KEY `Index_4` (`PointId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
CREATE TABLE `spphistograms` (
`SppId` smallint(5) NOT NULL DEFAULT '0',
`Prob` smallint(5) NOT NULL DEFAULT '0',
`CountOfPointId` int(10) DEFAULT NULL,
`CummulativeArea` int(10) DEFAULT NULL,
`PercentExtent` double(15,5) DEFAULT NULL,
PRIMARY KEY (`SppId`,`Prob`),
KEY `Index_2` (`SppId`),
KEY `Index_3` (`Prob`),
KEY `Index_4` (`PercentExtent`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
Table Status
==============
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free
sppmodelvalues 'InnoDB' 10 'Compact' 221000385 29 6499057664 0 11497619456 7340032
spphistograms 'InnoDB' 10 'Compact' 46587 56 2637824 0 2539520 4194304
Field Type Null Key Default Extra
PointId int(10) NO PRI
SppId smallint(5) NO PRI
Prob tinyint(3) NO MUL
Field Type Null Key Default Extra
SppId smallint(5) NO PRI 0
Prob smallint(5) NO PRI 0
CountOfPointId int(10) YES
CummulativeArea int(10) YES
PercentExtent double(15,5) YES MUL
Variable_name Value
bulk_insert_buffer_size 16777216
innodb_buffer_pool_size 20971520000
innodb_change_buffering inserts
innodb_log_buffer_size 67108864
join_buffer_size 268435456
key_buffer_size 20971520000
myisam_sort_buffer_size 1048576000
net_buffer_length 16384
preload_buffer_size 16777216
read_buffer_size 268435456
read_rnd_buffer_size 10485760
sort_buffer_size 1048576000
sql_buffer_result OFF