Improving MySQL query performance for a large database

Question

I 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");

Query explain

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?

`COUNT` queries are tough to optimize because, by definition, they need to count every record in thier scope. An index on `project.Date` would be a good idea, but it seems you already have this, so I am out of ideas. — Tim Biegeleisen, Sep 10 '19 at 13:34
Try using a Covering Index in the second table as well. So basically, create a new composite index `(ProjectId,BrandId)` on the `projectitem` table. This should be able to avoid data lookups and everything would be `using index` only. Should speed up things a bit. — Madhur Bhaiya, Sep 10 '19 at 13:37
Is the ID column used in search? I think the PRIMARY KEY in projectitem better to be: (`ProjectId`,`Id`,`SpecificationId`). And you can drop the index on (`ProjectId`) — PeterHe, Sep 10 '19 at 13:49

Improving MySQL query performance for a large database

0 Answers0