1. Reference: Analysis and optimization of timeout when querying total table records in MySQL 8.0. The execution time in Navicat is 3.075 seconds. See Figure 1.
SELECT
count(*) AS AGGREGATE
FROM
`tables`
WHERE
`id` > 0
2. The execution time in Laravel 9 is 60317.74ms, which is equivalent to 60 seconds. See Figure 2.
3. I verified another count(*) SQL query, and its execution time in Laravel 9 is 2623.17ms, which is equivalent to 2.6 seconds. Although it returns the same query results as the previous query, its execution time is only 5% of the previous SQL. See 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. Executing the raw SQL via the Tinker command (REPL) inside the container also takes about 3 seconds. See Figure 4.
$count = DB::select('SELECT count(*) AS AGGREGATE FROM `tables` WHERE `id` > 0');
5. Executing the raw SQL in the program takes about 3 seconds. See Figure 5.
$count = \Illuminate\Support\Facades\DB::select('SELECT count(*) AS AGGREGATE FROM `tables` WHERE `id` > 0');
print_r($count);
exit;
6. I finally confirmed that in vendor/laravel/framework/src/Illuminate/Database/Query/Builder.php, the runPaginationCountQuery method took 60 seconds to execute. Since this is a framework-level implementation, it is not easy to fix for now. I decided to use where `shipping_type` in (1, 2). The SQL execution time dropped to about 3 seconds, as expected. Below is the printed log to confirm the execution time of the runPaginationCountQuery method. The time between runPaginationCountQuery2 and $results2 is the execution time of the following code. See Figure 6 and 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"]