InfoBright

Infobright optimal ServerMainHeapSize is set to 10000(MB)
Infobright optimal ServerCompressedHeapSize is set to 1000(MB)
Infobright optimal LoaderMainHeapSize is set to 800(MB)

CREATE TABLE `pagestat` (
   `date_id` smallint  NOT NULL COMMENT 'LOOKUP',
   `project_id` smallint  NOT NULL COMMENT 'LOOKUP',
   `page_id` int NOT NULL,
   `page_count` int NOT NULL )
 engine = BRIGHTHOUSE;


CREATE TABLE `pages` (   `id` int(10)   ,   `page` varchar(1024)  ) engine=BrightHouse;

CREATE TABLE `datesinfo` (
   `id` smallint,
   `datestring` char(11),
   `caldate` date,
   `calday` int,
   `calmonth` tinyint,
   `calyear` smallint,
   `dayofweek` tinyint,
   `dayhour` tinyint
  ) engine=BrightHouse;

CREATE TABLE `projects` ( `id` smallint,  `project` varchar(50) ) Engine=BrightHouse;

LOAD DATA INFILE '/data/vol2/wikistat/in/200906.out' INTO TABLE pagestat FIELDS TERMINATED BY '|';

mysql> LOAD DATA INFILE '/data/vol2/wikistat/in/pages.txt' INTO TABLE pages FIELDS TERMINATED BY '|';
Query OK, 724533199 rows affected (1 hour 8 min 11.38 sec)
Records: 724533199  Deleted: 0  Skipped: 0  Warnings: 0

mysql> LOAD DATA INFILE '/data/vol2/wikistat/in/200906.out' INTO TABLE pagestat FIELDS TERMINATED BY '|';
 Query OK, 3453013109 rows affected (1 hour 26 min 22.45 sec)

mysql> LOAD DATA INFILE '/data/vol2/wikistat/in/200907.out' INTO TABLE pagestat FIELDS TERMINATED BY '|';
Query OK, 3442375618 rows affected (1 hour 26 min 12.82 sec)

mysql> LOAD DATA INFILE '/data/vol2/wikistat/in/datesinfo.txt' INTO TABLE datesinfo FIELDS TERMINATED BY '|';                 
Query OK, 9624 rows affected (0.33 sec)

mysql> LOAD DATA INFILE '/data/vol2/wikistat/in/projects.txt' INTO TABLE projects FIELDS TERMINATED BY '|';                  
Query OK, 2025 rows affected (0.16 sec)

• Q_TOP20_PROJECTS_2009_7 - 20 rows in set (40 min 8.30 sec)

mysql> SELECT project FROM projects LEFT OUTER JOIN pagestat ON (pagestat.project_id = projects.id) JOIN datesinfo ON (pagestat.date_id=datesinfo.id AND calmonth=7 and calyear=2009 AND calday=14 ) WHERE pagestat.project_id IS NULL ORDER BY project; Empty set (0.00 sec)

SELECT project FROM projects LEFT OUTER JOIN pagestat ON (pagestat.project_id = projects.id) WHERE pagestat.project_id IS NULL ORDER BY project; 65 rows in set (1 hour 30 min 12.00 sec)

• Q_NOPROJECTS_1_2009_7_14 - 613 rows in set (5.95 sec)

• NA

mysql>  SELECT page, sum(page_count) sm
    ->  FROM pagestat
    ->    JOIN datesinfo di ON ( di.id=date_id )
    ->    JOIN projects p ON  (p.id=project_id )
    ->    JOIN pages pp ON (pp.id=page_id)
    ->  WHERE di.calmonth=6 and di.calyear=2009 AND di.calday=18
    ->    AND project='de'
    ->  GROUP BY page
    ->  ORDER BY sm DESC
    ->  LIMIT 50;
not finished in 2h

SELECT page_id, sum(page_count) sm   FROM pagestat     JOIN datesinfo di ON ( di.id=date_id )     JOIN projects p ON  (p.id=project_id )   WHERE di.calmonth=6 and di.calyear=2009 AND di.calday=18     AND project='de'   GROUP BY page_id   ORDER BY sm DESC   LIMIT 50;
(2 min 1.33 sec)

SELECT page,sm FROM (SELECT page_id, sum(page_count) sm   FROM pagestat     JOIN datesinfo di ON ( di.id=date_id )     JOIN projects p ON  (p.id=project_id )   WHERE di.calmonth=6 and di.calyear=2009 AND di.calday=18     AND project='de'   GROUP BY page_id   ORDER BY sm DESC   LIMIT 50) t JOIN pages ON (id=t.page_id) ORDER BY sm DESC;
50 rows in set (3 min 12.75 sec)

• Q_NOACCESS_SECOND_DAY 7 rows in set (7 min 6.36 sec)