MySQL Forums
Forum List  »  Optimizer & Parser

Re: NOP optimises Group By? -solved
Posted by: Rick James
Date: September 13, 2010 10:56AM

:) Yes, WHERE is better.

innodb_log_buffer_size 67108864 -- 64M (default?)
key_buffer_size 20971520000 -- 20G ! Yikes!

You seem to be using InnoDB and have big table, yet the caches are tuned for MyISAM. If you are using mostly InnoDB, suggest

innodb_log_buffer_size -- 70% of _available_ RAM
key_buffer_size 50M

This will help performance, perhaps a lot.

PRIMARY KEY (`SppId`,`PointId`) USING BTREE,
KEY `Index_2` (`SppId`),
The KEY could be dropped -- the PRIMARY KEY provides an efficient way to look up by SppID.

`PercentExtent` double(15,5) DEFAULT NULL,
rounds to 5 decimal places before storing -- probably not what you want. Suggest
`PercentExtent` double DEFAULT NULL,
or
`PercentExtent` float DEFAULT NULL,
if 6 digits of precision is ok. (Also it will shrink the table by 4 bytes per row, probably not a big deal for this table.)

Options: ReplyQuote


Subject
Views
Written By
Posted
3948
September 05, 2010 08:16PM
1361
September 11, 2010 01:22PM
1587
September 12, 2010 11:09PM
Re: NOP optimises Group By? -solved
1412
September 13, 2010 10:56AM
1431
September 13, 2010 08:15PM
1357
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.