Support the ongoing development of Laravel.io →
Database Eloquent
Last updated 2 years ago.
0

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
0

Note: Everything is correct using explain for this mysql.

0

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.

0

code and DB on the same machine? or separate... can you give more environment details.

0

Hi Citti code and db are in different servers.

0

Sign in to participate in this thread!

Eventy

Your banner here too?

heremiguel heremiguel Joined 29 Jan 2015

Moderators

We'd like to thank these amazing companies for supporting us

Your logo here?

Laravel.io

The Laravel portal for problem solving, knowledge sharing and community building.

© 2024 Laravel.io - All rights reserved.