Announcement

Announcement Module
Collapse
No announcement yet.

Group by, index and aggregate functions...

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Group by, index and aggregate functions...

    According to the MySQL doc it seems that a group by statement having a sum() function in the select part doesn't use any index...

    I have a large table (around 11 millions lines) I need to do a group by and a sum on it. Do you have any advice???

  • #2
    Can you please provide us with SHOW CREATE TABLE and example query?

    Comment


    • #3
      Yep sorry,

      Here is the table:


      CREATE TABLE `READERSHIP` ( `RDS_MBR_ID` varchar(60) NOT NULL, `RDS_CNT_ID` varchar(255) NOT NULL, `RDS_CNT_PK` varchar(40) default NULL, `RDS_DURATION` float NOT NULL default '0', `RDS_LOCATION` varchar(255), `RDS_LOCATION_PK` varchar(40) default NULL, `RDS_REFERER` varchar(255), `RDS_REFERER_PK` varchar(40) default NULL, `RDS_CNT_TYPE` varchar(30) default NULL, `RDS_FILENAME` varchar(255) default NULL, `RDS_SITE` varchar(50) default NULL, KEY `RDS_MBR_ID` (`RDS_MBR_ID`), KEY `RDS_FILENAME` (`RDS_FILENAME`), KEY `RDS_SITE` (`RDS_SITE`), KEY `RDS_CNT_PK` (`RDS_CNT_PK`,`RDS_SITE`), KEY `RDS_DAY` (`RDS_DAY`)) ENGINE=MyISAM DEFAULT CHARSET=latin1;


      And here is the query:


      select RDS_MBR_ID, RDS_CNT_ID, RDS_SITE, RDS_LOCATION, RDS_REFERER, RDS_CNT_TYPE, RDS_FILENAME, sum(RDS_DURATION)from READERSHIPgroup by RDS_MBR_ID, RDS_CNT_PK, RDS_DAY, RDS_SITE, RDS_LOCATION_PK, RDS_REFERER_PK, RDS_CNT_TYPE, RDS_FILENAMEorder by NULL;


      I suppose playing with variables tmp_table_size, max_heap_table_size won't be useful as Readership size is around 1 giga bytes (it will definitely have to create a temporary table on disk)!

      Hope I haven't said anything too stupid )

      Comment

      Working...
      X