The same Count(*) query SQL, in Navicat, and in Laravel 9, the analysis of the difference in execution time is too large
1. Reference:In MySQL 8.0, when querying the total number of table records, the analysis optimization of the timeout. The execution time in Navicat is: 3.075 seconds. as shown in Figure 1
Figure 1
SELECT
count(*) AS AGGREGATE
FROM
`tables`
WHERE
`id` > 0
2. The execution time in Laravel 9 is: 60317.74ms. It is equivalent to 60 seconds. as shown in Figure 2
Figure 2
3. Verify another count(*) SQL, the execution time in Laravel 9 is: 2623.17ms. It is equivalent to 2.6 seconds. Although its query result is the same as the query result above. However, the execution time is only 5% of the above SQL. as shown in Figure 3
Figure 3
select
count(*) as aggregate
from
`tables`
where
`shipping_at_gmt` >= '2024-01-01 16:00:00'
and `shipping_at_gmt` <= '2024-05-06 15:59:59'
4. In the container, the native SQL is executed based on the Tinker command (REPL), and the time is also about 3 seconds. as shown in Figure 4
Figure 4
$count = DB::select('SELECT count(*) AS AGGREGATE FROM `tables` WHERE `id` > 0');
5. Execute the native SQL in the program for about 3 seconds. as shown in Figure 5
Figure 5
$count = \Illuminate\Support\Facades\DB::select('SELECT count(*) AS AGGREGATE FROM `tables` WHERE `id` > 0');
print_r($count);
exit;
6. Finally confirmed that in vendor/Laravel/framework/src/illuminate/database/query/builder.php, the method runPaginationCountQuery executes 60 second. This is the implementation of the bottom layer of the framework, and it is not easy to solve for the time being. Decided to use WHERE `Shipping_Type` in (1, 2). The SQL execution time is about 3 seconds, which is in line with expectations. The following are the printed logs to confirm the execution time of RunPaginationCountQuery. The time between RunPaginationCountQuery2 and $results2 is the execution time of the following code. As shown in Figure 6, Figure 7
Figure 6
Figure 7
return $this->cloneWithout($without)
->cloneWithoutBindings($this->unions ? ['order'] : ['select', 'order'])
->setAggregate('count', $this->withoutSelectAliases($columns))
->get()->all();
[2024-05-06 10:14:07] local.INFO: $total1 ["2024-05-06 10:14:07"]
[2024-05-06 10:14:07] local.INFO: $results1 ["2024-05-06 10:14:07"]
[2024-05-06 10:14:07] local.INFO: runPaginationCountQuery2 ["2024-05-06 10:14:07"]
[2024-05-06 10:15:38] local.INFO: $results2 ["2024-05-06 10:15:38"]
[2024-05-06 10:15:38] local.INFO: $total2 ["2024-05-06 10:15:38"]
