Slow MySQL query in Laravel, but very fast in phpMyAdmin-CodePudding

I have worked with one query in Laravel, i need to optimize it. At first i have such query

DB::select(DB::raw("select `discounts`.`id`, `discounts`.`discount` from `discounts` left join `discount_companies_criteria` on `discounts`.`id` = `discount_companies_criteria`.`discount_id` and `discount_companies_criteria`.`is_active` = 1 where `discount_companies_criteria`.`company_id` is null and `discounts`.`status` = 1 group by `discounts`.`id`")) order by discounts.discount ASC;

laravel debugbar shows - 579ms

in phpmyadmin this query run by 503ms

than i have refactored code and get modified query (just remove sorting)

DB::select(DB::raw("select `discounts`.`id`, `discounts`.`discount` from `discounts` left join `discount_companies_criteria` on `discounts`.`id` = `discount_companies_criteria`.`discount_id` and `discount_companies_criteria`.`is_active` = 1 where `discount_companies_criteria`.`company_id` is null and `discounts`.`status` = 1 group by `discounts`.`id`")) ;

laravel debugbar shows - 579ms

in phpmyadmin this query run by 3ms

Can somebody explain, how i can get the same speed in laravel as in phpMyadmin?

Table discounts - 1900 rows

Table discount_companies_criteria - 7500000 rows

CodePudding user response：

General tip, don't ever run query without limit "select, delete, or update", in your case use pagination

First the following query has no limit, which obviously would have bad performance depends on the size of the data you're dealing with:

SELECT `discounts`.`id`,
       `discounts`.`discount`
FROM `discounts`
LEFT JOIN `discount_companies_criteria` ON `discounts`.`id` = `discount_companies_criteria`.`discount_id`
AND `discount_companies_criteria`.`is_active` = 1
WHERE `discount_companies_criteria`.`company_id` IS NULL
  AND `discounts`.`status` = 1
GROUP BY `discounts`.`id`

The reason why this query or the other one are faster in phpMyAdmin is because phpMyAdmin by default set limit to the query.