Note that there are some explanatory texts on larger screens.

plurals
  1. POOptimizing GROUP BY performance - Adds roughly five seconds to query
    primarykey
    data
    text
    <p>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).</p> <p>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 <code>0.08</code> seconds and slows it down to <code>5.89</code> on average.</p> <p>An example query:</p> <pre><code>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 &gt;= '2013-01-31 17:09:16' GROUP BY prism_actions.block_id LIMIT 1000; </code></pre> <p>I've tried a wide variety of different queries our app might use and group by is one of largest performance hits.</p> <p>Our current table structure:</p> <pre><code>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 ; </code></pre> <p>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).</p> <p>How can I improve the performance of the grouping?</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload