SELECT count(*) is very slow

So you are comparing apples and pears.

You can't compare how long time something in the database database takes against how long a php script that connects to the database and then performs the same query. The php script needs to be parsed and executed on the server which takes some time and if that script connects to the database and then does the same query as the script in the database and returns the result to the server so that it can continue to parse the page.

I suggest you install debugbar instead of trying to compare the whole php execution including the database fetch to running an SQL query directly in the database.

https://github.com/barryvdh/laravel-debugbar

I'm sorry, but I really don't compare apples to pears. See my new post below.

There is no reason to install another debugbar, I wrote that I use Clockwork. In addition, I have the actual duration of queries measured directly in the code.

You can't compare

SELECT * FROM users;
SELECT COUNT(*) FROM users;

To

Route::get('/users', function () {
    $milliseconds = round(microtime(true) * 1000);
    $users = \App\Models\User::all();
    $timeSelect = round(microtime(true) * 1000) - $milliseconds;

    $milliseconds = round(microtime(true) * 1000);
    $count = \App\Models\User::count();
    $timeCount = round(microtime(true) * 1000) - $milliseconds;

    return([
        'count' => $count,
        'timeSelect' => $timeSelect,
        'timeCount' => $timeCount,
        'users' => $users->toArray(),
    ]);
});

The top one will always be faster since it doesn't need to connect to the database and run those queries since you are already in the database when you execute the SQL.

Like I said apples and pears.

I don't understand what you mean. The upper example concerns the selection of everything, the lower the selection of the number. And just the second query (for the number of records) is slower than the first ...

Like I said install the debugbar then you will see how long the query it self took and how long the application took.

Take this query for example

select `authors`.*, (select count(*) from `books` inner join `author_books` on `books`.`id` = `author_books`.`book_id` where `authors`.`id` = `author_books`.`author_id`) as `books_count` from `authors` order by `last_name` asc, `first_name` asc

Run in laravel It took 1.81ms in the database according to the debugbar. The whole request on the other hand took 31.52 ms.

Running it from the MySQL prompt

MariaDB [mediabase]> select `authors`.*, (select count(*) from `books` inner join `author_books` on `books`.`id` = `author_books`.`book_id` where `authors`.`id` = `author_books`.`author_id`) as `books_count` from `authors` order by `last_name` asc, `first_name` asc;
+----+------------+-----------+-------------------+---------------------+---------------------+-------------+
| id | first_name | last_name | slug              | created_at          | updated_at          | books_count |
+----+------------+-----------+-------------------+---------------------+---------------------+-------------+
|  5 | Sarah      | Ash       | ash-sarah         | 2020-12-05 10:56:20 | 2020-12-05 10:56:20 |           0 |
|  4 | Anne       | Bishop    | bishop-anne       | 2020-12-05 10:56:20 | 2020-12-05 10:56:20 |           0 |
|  3 | Patricia   | Briggs    | briggs-patricia   | 2020-12-05 10:56:20 | 2020-12-05 10:56:20 |           0 |
|  8 | David      | Eddings   | eddings-david     | 2020-12-05 10:56:20 | 2020-12-05 10:56:20 |           1 |
|  2 | Terry      | Goodkind  | goodkind-terry    | 2020-12-05 10:56:20 | 2020-12-05 10:56:20 |           0 |
|  6 | Faith      | Hunter    | hunter-faith      | 2020-12-05 10:56:20 | 2020-12-05 10:56:20 |           0 |
|  1 | Robert     | Jordan    | jordan-robert     | 2020-12-05 10:56:20 | 2020-12-05 10:56:20 |           3 |
|  7 | Brandon    | Sanderson | sanderson-brandon | 2020-12-05 10:56:20 | 2020-12-05 10:56:20 |           1 |
+----+------------+-----------+-------------------+---------------------+---------------------+-------------+
8 rows in set (0.001 sec)

You know https://github.com/itsgoingd/clockwork? ... :)

I use a debugbar and I know how long the overall request takes and how long specific queries to the database take.

Once again. Queries run at one time, in the order in which they are listed.

$mysqli->query("SELECT * from users"); // why is fast?
$mysqli->query("SELECT count(*) from users"); // why is slow?
$mysqli->query("SELECT * from users"); // why is fast?
$mysqli->query("SELECT count(*) from users"); // why is slow?

i'd get onto DO about this. Sounds like an issue with the DB server.

I use mysql. But problem is with server. I think...

Do you have the opportunity to try on a sharp server? I'll be grateful ... I've tried two already.

Not necessary, you 'll have different values in absolute but not in relative (ie count should be always shorter than select all)