Optimizing GROUP BY performance - Adds roughly five seconds to query

Question

I'm running a wide variety of profiling on a large database that's typical for users of our application. It stores millions of records and I've spent time making sure the field types are what they need to be (although we could also normalize and move three of these columns to foreign keys).

The issue that by default, queries group by related information and count repetitive records. This group by kills us - taking a query that runs in 0.08 seconds and slows it down to 5.89 on average.

An example query:

SELECT player, x, y, z, COUNT(id), action_type
FROM prism_actions WHERE world = 'world'
AND (prism_actions.x BETWEEN -1119.650147217701 AND -919.650147217701)
AND (prism_actions.y BETWEEN -33.0 AND 167.0)
AND (prism_actions.z BETWEEN 385.14867792476133 AND 585.1486779247614) 
AND prism_actions.action_time >= '2013-01-31 17:09:16'
GROUP BY prism_actions.block_id 
LIMIT 1000;

I've tried a wide variety of different queries our app might use and group by is one of largest performance hits.

Our current table structure:

CREATE TABLE IF NOT EXISTS `prism_actions` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `action_time` timestamp NOT NULL default CURRENT_TIMESTAMP,
  `action_type` varchar(25) NOT NULL,
  `player` varchar(16) NOT NULL,
  `world` varchar(255) NOT NULL,
  `x` int(11) NOT NULL,
  `y` int(11) NOT NULL,
  `z` int(11) NOT NULL,
  `block_id` mediumint(5) default NULL,
  `block_subid` mediumint(5) default NULL,
  `old_block_id` mediumint(5) default NULL,
  `old_block_subid` mediumint(5) default NULL,
  `data` varchar(255) default NULL,
  PRIMARY KEY  (`id`),
  KEY `x` (`x`),
  KEY `action_type` (`action_type`),
  KEY `player` (`player`),
  KEY `block_id` (`block_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=44525743 ;

We normally group by three fields but those don't impact performance much. I've tried playing with indexes (some have recommended combined indexes for other issues we've had, but there are so many variations of queries a user might request of the app - no way to really know which fields they'll be using).

How can I improve the performance of the grouping?

Answer 1

Try the following changes:

1. create a composite index that contains world, block_id, x, y, z and action time
2. make block_id "not null"
3. for x, y, z condition, round up the values since x, y, z are integers anyway