Question regarding aggregate in Query Builder

replied 9 years ago

The count is needed for the paginate.

If you just did take(10)->get() then it would only do what you asked.

perhaps you dont need pagination

--

but i cant believe it takes 12 seconds to count 15million rows. Is there perhaps something you can do to that table to speed it up?

0

heremiguel

replied 9 years ago

Yes because the table has 2 additional joins and for any reason this joins are included in the count query:

$reports = DB::table('Reports')
					->select(DB::raw($this->fields))
					->join('Item', 'Reports.ItemId', '=', 'Item.ItemId')
					->join('User', 'Reports.UId', '=', 'User.UId')
					->Where('Reports.ReportId', '=', $textToSearch)
					->orWhere('Reports.ItemId', '=', $textToSearch)
					->orderBy($sortField, $sortOrder)
					->paginate($pageSize);

This is in the debugger: Records in this table 227435 and it takes near 1 sec.


SELECT count(*) as aggregate FROM `Reports` inner join `Item` on `Reports`.`ItemId` = `Item`.`ItemId` inner join `User` on `Reports`.`UId` = `User`.`UId`	1,022.400 ms

0

kkiernan

replied 9 years ago

Are you able to share your table structures?

SHOW CREATE TABLE table;

You might be missing some useful indexes or foreign keys. I've seen a few times where a query taking 10+ seconds goes down to under a second from adding a single BTREE index. Double check that there are in fact indexes or keys defined for ReportId and ItemId.

Have you tried running an explain plan on the query outputted from the debugger? I use explain plans to see where queries might be running a full table scan, and then try to eliminate that first.

0

heremiguel

replied 9 years ago

Hi, thank you for your help:

I made some improvements I removed the join to the User table and create a calculated field in the Select statement and now it takes 50% less time.


SELECT * FROM `User` WHERE `User`.`UId` = '1054087' LIMIT 1	
Duration 252.960 ms

SELECT count(*) as aggregate FROM `Reports` inner join `Item` on `Reports`.`ItemId` = `Item`.`ItemId` WHERE `Item`.`Title` LIKE '%sleeve%' or `Item`.`Author` LIKE '%sleeve%'	
Duration 934.740 ms (before was 2.237.007ms)

SELECT (SELECT Email FROM User WHERE UId = Reports.UId ) as Email, ( SELECT Genre.GenreDescription FROM Genre WHERE Genre.GenreId = Item.GenreId) as GenreDescription, Reports.ReportId, (SELECT Settings_Report.ReportTypeDescription FROM GlobalSettings.Settings_Report WHERE GlobalSettings.Settings_Report.ReportTypeId = Reports.ReportType ) as ReportTypeDescription, (SELECT GROUP_CONCAT(Location) FROM Asset WHERE ItemId = Reports.ItemId ORDER BY AssetOrder) as Location, Item.Type, (SELECT GROUP_CONCAT(CONCAT_WS("$",COALESCE(Country,""),COALESCE(Gender,""),COALESCE(AgeMin,""),COALESCE(AgeMax,""),InReq,InRec,Priority,Bonus,COALESCE(toReviewId,""))) FROM toReview WHERE ReportId = Reports.ReportId and InReq > 0 ORDER BY Country, Gender) as Progress, Reports.Anonymous, (SELECT M.MPRank FROM GlobalStats.ReportGenreMktPot M WHERE M.GenreId <> 0 and M.GenderId=0 and M.AgeRangeId = 0 and M.CountryId = if(Reports.ReportType=5,"GB", if(Reports.ReportType=6, "US", "00")) and M.ReportId = Reports.ReportId) as MktPotGen, (SELECT M.MPRank FROM GlobalStats.ReportGenreMktPot M WHERE M.GenreId = 0 and M.GenderId=0 and M.AgeRangeId = 0 and M.CountryId = if(Reports.ReportType=5,"GB", if(Reports.ReportType=6, "US", "00")) and M.ReportId = Reports.ReportId) as MktPot, (SELECT SumWeightedScore/SumInfFac FROM GlobalStats.ReportStats WHERE ReportId = Reports.ReportId) as Score, Reports.Status, Item.Author, Item.Title, Reports.UId, Reports.Metadata as ReportsMetadata, Item.ItemId, Item.Metadata as ItemMetadata, Reports.ReportType, Reports.OrderDateTime FROM `Reports` inner join `Item` on `Reports`.`ItemId` = `Item`.`ItemId` WHERE `Item`.`Title` LIKE '%sleeve%' or `Item`.`Author` LIKE '%sleeve%' ORDER BY `Reports`.`ReportId` DESC LIMIT 15 offset 0	
Duration: 365.230 ms

Pagination obviously has to count all records and if I have a join using the join-> statement in query builder, I get the table included in the count(*), increasing execution times to numbers that we couldn't afford.

Find below the tables structure.

Reports Table

CREATE TABLE `Reports` (
  `ReportId` int(11) NOT NULL AUTO_INCREMENT,
  `UId` int(11) NOT NULL DEFAULT '0',
  `ItemId` int(11) NOT NULL DEFAULT '0',
  `ReportGenreId` int(11) DEFAULT NULL,
  `AskReport` varchar(250) DEFAULT '10004|10005',
  `AskItem` varchar(250) DEFAULT '10000|10001',
  `ReportType` int(11) NOT NULL DEFAULT '1',
  `OrderDateTime` int(11) DEFAULT NULL,
  `ReturnAfter` int(11) DEFAULT NULL,
  `DeadlineDateTime` int(11) DEFAULT NULL,
  `GenerationDateTime` int(11) DEFAULT NULL,
  `DeliveryDateTime` int(11) DEFAULT NULL,
  `DeadlineHours` int(11) DEFAULT NULL,
  `ReviewsReq` int(11) DEFAULT NULL,
  `Reviews` int(11) DEFAULT '0',
  `InvoiceYear` int(11) DEFAULT NULL,
  `InvoiceMonth` int(11) DEFAULT NULL,
  `Cancelled` tinyint(1) DEFAULT NULL,
  `ToInvoice` tinyint(1) DEFAULT NULL,
  `Anonymous` tinyint(1) NOT NULL DEFAULT '0',
  `Status` varchar(50) DEFAULT NULL,
  `TrackRating` decimal(4,1) DEFAULT '0.0',
  `RatingDistribution` varchar(250) DEFAULT '',
  `MktPot` int(11) DEFAULT NULL,
  `MktPotGenre` int(11) DEFAULT NULL,
  `MktPotAge` varchar(100) DEFAULT '',
  `MktPotAgeGenre` varchar(100) DEFAULT '',
  `InGenreClass` varchar(25) DEFAULT '',
  `SampleGroupGender` varchar(50) DEFAULT '',
  `SampleGroupAge` varchar(100) DEFAULT '',
  `PassionRating` decimal(4,1) DEFAULT '0.0',
  `TrackAppeal` varchar(100) DEFAULT '',
  `ArtistsMentioned` text,
  `SongAnalysis` varchar(250) DEFAULT '',
  `WordCloud` longtext,
  `PositioningThisTrack` varchar(50) DEFAULT '',
  `PositioningGenreOther` longtext,
  `PositioningLabelOther` longtext,
  `prid` varchar(50) DEFAULT '',
  `callback_url` varchar(250) DEFAULT '',
  `Metadata` longtext,
  PRIMARY KEY (`ReportId`),
  KEY `idx_Reports_UId` (`UId`),
  KEY `idx_Reports_ItemId` (`ItemId`),
  KEY `idx_Reports_ReportType` (`ReportType`),
  KEY `idx_Reports_OrderDateTime` (`OrderDateTime`),
  KEY `idx_Reports_DeadlineDateTime` (`DeadlineDateTime`),
  KEY `idx_Reports_ToInvoice` (`ToInvoice`),
  KEY `idx_Reports_Status` (`Status`),
  KEY `idx_Reports_TrackRating` (`TrackRating`),
  KEY `idx_Reports_MktPot` (`MktPot`),
  KEY `idx_Reports_MktPotGenre` (`MktPotGenre`),
  KEY `idx_Reports_InvoiceYear` (`InvoiceYear`),
  KEY `idx_Reports_InvoiceMonth` (`InvoiceMonth`)
) ENGINE=InnoDB AUTO_INCREMENT=5144383 DEFAULT CHARSET=utf8

User table:

CREATE TABLE `User` (
  `UId` int(11) NOT NULL AUTO_INCREMENT,
  `Email` varchar(100) NOT NULL DEFAULT '',
  `Password` varchar(250) NOT NULL DEFAULT '',
  `LocationCoords` varchar(50) DEFAULT '',
  `Street` varchar(250) DEFAULT '',
  `City` varchar(250) DEFAULT '',
  `State` varchar(250) DEFAULT '',
  `Country` varchar(100) DEFAULT '',
  `PostCode` varchar(50) DEFAULT '',
  `FirstName` varchar(800) DEFAULT '',
  `LastName` varchar(800) DEFAULT '',
  `Confirmed` tinyint(11) DEFAULT '0',
  `termsAgreement` int(11) NOT NULL DEFAULT '0',
  `CountryCode` varchar(10) DEFAULT '',
  `IP` varchar(50) DEFAULT '',
  `Daddy` int(11) DEFAULT '0',
  `DaddyBonus` int(11) DEFAULT '0',
  `DaddyExpiry` int(11) DEFAULT '0',
  `Registered` int(11) DEFAULT NULL,
  `LastLogin` int(11) DEFAULT NULL,
  `DoB` int(11) DEFAULT NULL,
  `SUB_SO` int(11) DEFAULT '0',
  `SUB_STP` int(11) DEFAULT '0',
  `SUB_TOTD` int(11) DEFAULT '0',
  `Locked` int(11) NOT NULL DEFAULT '0',
  `AccBalance` decimal(18,3) DEFAULT '0.000',
  `Gender` tinyint(11) DEFAULT NULL,
  `Source` varchar(20) DEFAULT 'Unknown',
  `LockReason` varchar(50) DEFAULT NULL,
  `LastLock` int(11) DEFAULT '0',
  `GoodReviews` int(11) DEFAULT '0',
  `BadReviews` int(11) DEFAULT '0',
  `Type` tinyint(11) DEFAULT '0',
  `Influence` decimal(18,9) DEFAULT '1.000000000',
  `Groups` varchar(200) DEFAULT '1',
  `MetaData` longtext,
  `Alias` varchar(400) DEFAULT '',
  `TransactionsUpdated` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`UId`),
  KEY `idx_User_Email` (`Email`),
  KEY `idx_User_FirstName` (`FirstName`(255)),
  KEY `idx_User_LastName` (`LastName`(255)),
  KEY `idx_User_Alias` (`Alias`(255)),
  KEY `idx_User_Daddy` (`Daddy`),
  KEY `idx_User_CountryCode` (`CountryCode`),
  KEY `idx_User_Registered` (`Registered`),
  KEY `idx_User_LastLogin` (`LastLogin`),
  KEY `idx_User_DoB` (`DoB`),
  KEY `idx_User_SUB_SO` (`SUB_SO`),
  KEY `idx_User_SUB_STP` (`SUB_STP`),
  KEY `idx_User_SUB_TOTD` (`SUB_TOTD`),
  KEY `idx_User_Locked` (`Locked`),
  KEY `idx_User_Gender` (`Gender`),
  KEY `idx_User_AccBalance` (`AccBalance`),
  KEY `idx_User_LastLock` (`LastLock`),
  KEY `idx_User_Type` (`Type`),
  KEY `idx_User_Influence` (`Influence`),
  KEY `idx_User_Groups` (`Groups`),
  KEY `idx_User_TransactionsUpdated` (`TransactionsUpdated`),
  KEY `idx_User_Source` (`Source`),
  KEY `idx_User_Confirmed` (`Confirmed`)
) ENGINE=InnoDB AUTO_INCREMENT=1068621 DEFAULT CHARSET=utf8

Item Table:


CREATE TABLE `User` (
  `UId` int(11) NOT NULL AUTO_INCREMENT,
  `Email` varchar(100) NOT NULL DEFAULT '',
  `Password` varchar(250) NOT NULL DEFAULT '',
  `LocationCoords` varchar(50) DEFAULT '',
  `Street` varchar(250) DEFAULT '',
  `City` varchar(250) DEFAULT '',
  `State` varchar(250) DEFAULT '',
  `Country` varchar(100) DEFAULT '',
  `PostCode` varchar(50) DEFAULT '',
  `FirstName` varchar(800) DEFAULT '',
  `LastName` varchar(800) DEFAULT '',
  `Confirmed` tinyint(11) DEFAULT '0',
  `termsAgreement` int(11) NOT NULL DEFAULT '0',
  `CountryCode` varchar(10) DEFAULT '',
  `IP` varchar(50) DEFAULT '',
  `Daddy` int(11) DEFAULT '0',
  `DaddyBonus` int(11) DEFAULT '0',
  `DaddyExpiry` int(11) DEFAULT '0',
  `Registered` int(11) DEFAULT NULL,
  `LastLogin` int(11) DEFAULT NULL,
  `DoB` int(11) DEFAULT NULL,
  `SUB_SO` int(11) DEFAULT '0',
  `SUB_STP` int(11) DEFAULT '0',
  `SUB_TOTD` int(11) DEFAULT '0',
  `Locked` int(11) NOT NULL DEFAULT '0',
  `AccBalance` decimal(18,3) DEFAULT '0.000',
  `Gender` tinyint(11) DEFAULT NULL,
  `Source` varchar(20) DEFAULT 'Unknown',
  `LockReason` varchar(50) DEFAULT NULL,
  `LastLock` int(11) DEFAULT '0',
  `GoodReviews` int(11) DEFAULT '0',
  `BadReviews` int(11) DEFAULT '0',
  `Type` tinyint(11) DEFAULT '0',
  `Influence` decimal(18,9) DEFAULT '1.000000000',
  `Groups` varchar(200) DEFAULT '1',
  `MetaData` longtext,
  `Alias` varchar(400) DEFAULT '',
  `TransactionsUpdated` tinyint(4) DEFAULT '0',
  PRIMARY KEY (`UId`),
  KEY `idx_User_Email` (`Email`),
  KEY `idx_User_FirstName` (`FirstName`(255)),
  KEY `idx_User_LastName` (`LastName`(255)),
  KEY `idx_User_Alias` (`Alias`(255)),
  KEY `idx_User_Daddy` (`Daddy`),
  KEY `idx_User_CountryCode` (`CountryCode`),
  KEY `idx_User_Registered` (`Registered`),
  KEY `idx_User_LastLogin` (`LastLogin`),
  KEY `idx_User_DoB` (`DoB`),
  KEY `idx_User_SUB_SO` (`SUB_SO`),
  KEY `idx_User_SUB_STP` (`SUB_STP`),
  KEY `idx_User_SUB_TOTD` (`SUB_TOTD`),
  KEY `idx_User_Locked` (`Locked`),
  KEY `idx_User_Gender` (`Gender`),
  KEY `idx_User_AccBalance` (`AccBalance`),
  KEY `idx_User_LastLock` (`LastLock`),
  KEY `idx_User_Type` (`Type`),
  KEY `idx_User_Influence` (`Influence`),
  KEY `idx_User_Groups` (`Groups`),
  KEY `idx_User_TransactionsUpdated` (`TransactionsUpdated`),
  KEY `idx_User_Source` (`Source`),
  KEY `idx_User_Confirmed` (`Confirmed`)
) ENGINE=InnoDB AUTO_INCREMENT=1068621 DEFAULT CHARSET=utf8

Thank you very much for your help.

0

heremiguel

replied 9 years ago

I was thinking in another solution, would be possible to store the pagination results in "remember"? Maybe I am asking too much but it would be a good idea for performance.

I am not using the full version of paginate, just showing the previous and next button.

Cheers Jose

Last updated 9 years ago.

0

Moderators