MySQL Forums
Forum List  »  Optimizer & Parser

Re: NOP optimises Group By? -solved
Posted by: Peter Griffioen
Date: September 12, 2010 11:09PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
3947
September 05, 2010 08:16PM
1361
September 11, 2010 01:22PM
Re: NOP optimises Group By? -solved
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.

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.