have a simple query that do a join between two table. The main table has a date field that my query filter by a range of dates. But for a month period my main table has 37412 datas and for this values has others 914 794 items in child table. That just for a month period, for all time the child table has more than 35 975 568 items.
A simple join, like that, filtering by a range date is so slow:
select count(i.BrandId) from projectitem i
inner join project p on i.ProjectId = p.Id
where (p.Date between "2019-07-01 00:00:00" AND "2019-07-30 23:59:59");
That is the struct of tables:
'CREATE TABLE `project` (
`Id` char(36) NOT NULL,
`Url` varchar(10) DEFAULT NULL,
`Region` varchar(2) NOT NULL,
`Area` int(11) NOT NULL,
`Name` varchar(250) DEFAULT NULL,
`Description` text,
`AccountId` char(36) DEFAULT NULL,
`UserId` char(36) DEFAULT NULL,
`Date` datetime DEFAULT NULL,
`ModifiedDate` datetime DEFAULT NULL,
`DeletedDate` datetime DEFAULT NULL,
`Deleted` tinyint(4) NOT NULL,
`Likes` int(11) NOT NULL,
`Views` int(11) NOT NULL,
`Private` tinyint(4) NOT NULL,
`OnlyBudget` tinyint(4) NOT NULL,
PRIMARY KEY (`Id`),
KEY `dateproject` (`Date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci'
'CREATE TABLE `projectitem` (
`Id` char(36) NOT NULL,
`BrandId` char(36) DEFAULT NULL,
`SpecificationId` char(36) NOT NULL,
`ProjectId` char(36) NOT NULL,
PRIMARY KEY (`Id`,`ProjectId`,`SpecificationId`),
KEY `project_item_key_idx` (`ProjectId`),
KEY `brand_idx` (`BrandId`),
KEY `spec_item_key_idx` (`SpecificationId`),
CONSTRAINT `project_item_key` FOREIGN KEY (`ProjectId`) REFERENCES `project` (`Id`),
CONSTRAINT `spec_item_key` FOREIGN KEY (`SpecificationId`) REFERENCES `specification` (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci'
How I can improve that? I thing that one reason for the bad performance is because the lenght of my key in "projectitem" table.
I migrated that data from another database. Than, first a created all tables without keys and after the migration I used that command for create keys and indexes:
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
ALTER TABLE `projectitemreference`
ADD PRIMARY KEY (`Id`, `ProjectId`, `ProjectItemId`, `SpecificationId`),
ADD INDEX `reference_item_key_idx` (`ProjectId` ASC, `ProjectItemId` ASC, `SpecificationId` ASC) VISIBLE
, LOCK = NONE;
ALTER TABLE `projectitemreference`
ADD CONSTRAINT `reference_item_key`
FOREIGN KEY (`ProjectId` , `ProjectItemId` , `SpecificationId`)
REFERENCES .`projectitem` (`ProjectId` , `Id` , `SpecificationId`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
ADD CONSTRAINT `reference_project_key`
FOREIGN KEY (`ProjectId`)
REFERENCES .`project` (`Id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION, LOCK = NONE;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
Can it be a problem?
(projectid, brandid)
would help. (and you can see why a 32-bytes-per-row index will perform better than a 288-bytes-per-row one).