We have three tables(datacount, resourcetable, daterange) with one of them(count) having million+ rows in it. Resourcetable will have around 100 rows and daterange arnd 10.
We want to get the sum(cnt) from count table grouped by values in resource and/or daterange;
The structure is as follows :
create table resourcetable(resourceid char(36), resourcename varchar(255)) engine=InnoDB;
create table datacount( cnt integer, resourceid char(36), cntdate timestamp) engine=InnoDB;
create table daterange(datemin date, datemax date) engine=InnoDB;
We tried to optimize the performance by creating index on resourceid, cntdate and both of them but it doesn’t work.
All the queries are taking more than 30 seconds :
-
select sum(cnt), datemin, datemax from datacount, resourcetable, daterange where date(cntdate) between datemin and datemax and resourcename like ‘% 1%’ group by datemin, datemax;
-
select sum(cnt), datemin, datemax, resourcetable.resourceid from datacount, resourcetable, daterange where date(cntdate) between datemin and datemax and resourcename like ‘% 1%’
group by datemin, datemax, resourcetable.resourceid; -
select sum(cnt), resourcetable.resourceid from datacount, resourcetable, daterange where date(cntdate) between datemin and datemax and resourcename like ‘% 1%’
group by resourcetable.resourceid;
Scripts to insert data in tables :
(You can change the rowfactor in the INSERTCOUNT procedure to insert more rows. As of now it will insert around 200K rows)
insert into daterange values(‘2008-04-01’,‘2008-04-05’);
insert into daterange values(‘2008-04-06’,‘2008-04-10’);
insert into daterange values(‘2008-04-11’,‘2008-04-15’);
insert into daterange values(‘2008-04-16’,‘2008-04-20’);
insert into daterange values(‘2008-04-21’,‘2008-04-25’);
insert into daterange values(‘2008-04-26’,‘2008-04-30’);
delimiter //
CREATE PROCEDURE INSERTRESOURCE()
BEGIN
DECLARE itr INT DEFAULT 10;
WHILE itr < 99 DO
INSERT INTO resourcetable VALUES(concat(“00000000-0000-0000-0000-0000000000”,itr),concat( "Resource ",itr) );
SET itr = itr + 1;
END WHILE;
END;
//
CREATE PROCEDURE INSERTCOUNT()
BEGIN
DECLARE itr INT DEFAULT 10;
DECLARE citr INT DEFAULT 0;
DECLARE rowfactor INT DEFAULT 2000;
DECLARE intvl INT DEFAULT 2592000/rowfactor;
DECLARE stts TIMESTAMP DEFAULT ‘2008-04-01 00:00:00’;
SET AUTOCOMMIT=0;
OL : WHILE citr < rowfactor DO
SET itr = 10;
IL: WHILE itr < 99 DO
INSERT INTO datacount VALUES(3, concat(“00000000-0000-0000-0000-0000000000”,itr), stts );
SET itr = itr + 1;
END WHILE IL;
COMMIT;
SET citr = citr + 1;
SET stts = TIMESTAMPADD(SECOND, intvl, stts);
END WHILE OL;
END;
//
delimiter ;
call INSERTRESOURCE();
call INSERTCOUNT();
create index resource_index on datacount(resourceid(36));
create index time_index on datacount(cntdate);
Any help would be really appreciated.