I have used "take" + "get" instead "paginate" and getting similar execution time.
SELECT (SELECT GenreDescription FROM Genre WHERE GenreId = Reports.ReportGenreId) as ReportGenre, Review.Influence, Review.InfluenceCalc, (SELECT Location FROM Asset WHERE Asset.ItemId = Item.ItemId LIMIT 1) as Location, (SELECT ReportTypeDescription FROM GlobalSettings.Settings_Report WHERE ReportTypeId = Reports.ReportType) as ReportTypeDescription, Review.CountryCode, Review.Active, Reports.ReportType, Review.InfluenceCalc, Review.Metadata as ReviewMetadata, Reports.Metadata as ReportsMetadata, Item.Metadata as ItemMetadata, Item.Title, Item.Author, Review.Time, Review.ReviewId, Review.Comment, Review.Score, Item.ItemId, Reports.ReportId, Review.BotLev, Item.Type, Review.UId FROM `Reports` inner join `Item` on `Reports`.`ItemId` = `Item`.`ItemId` inner join `Review` on `Reports`.`ReportId` = `Review`.`ReportId` ORDER BY `Review`.`ReviewId` DESC LIMIT 16
Duration 2,843.690 ms
Changing the order by field it takes less time to execute BUT I need the order by other field.
SELECT (SELECT GenreDescription FROM Genre WHERE GenreId = Reports.ReportGenreId) as ReportGenre, Review.Influence, Review.InfluenceCalc, (SELECT Location FROM Asset WHERE Asset.ItemId = Item.ItemId LIMIT 1) as Location, (SELECT ReportTypeDescription FROM GlobalSettings.Settings_Report WHERE ReportTypeId = Reports.ReportType) as ReportTypeDescription, Review.CountryCode, Review.Active, Reports.ReportType, Review.InfluenceCalc, Review.Metadata as ReviewMetadata, Reports.Metadata as ReportsMetadata, Item.Metadata as ItemMetadata, Item.Title, Item.Author, Review.Time, Review.ReviewId, Review.Comment, Review.Score, Item.ItemId, Reports.ReportId, Review.BotLev, Item.Type, Review.UId FROM `Review` inner join `Reports` on `Reports`.`ReportId` = `Review`.`ReportId` inner join `Item` on `Reports`.`ItemId` = `Item`.`ItemId` ORDER BY `Reports`.`ReportId` DESC LIMIT 15 offset 0
Duration 427.680 ms
The strange thing is, both queries have the same execution time in Sequel Pro.
code and DB on the same machine? or separate... can you give more environment details.
Sign in to participate in this thread!
The Laravel portal for problem solving, knowledge sharing and community building.
The community