Could withCount performance be improved?

[tontonsb]

• Laravel Version: 5.6.2
• PHP Version: 7.2
• Database Driver & Version: MySQL 5.7.23-0ubuntu0.18.04.1

Description:

Currently withCount produces the following query:

select 
	`artists`.*, 
	(
		select count(*) 
		from `songs` 
		where `artists`.`id` = `songs`.`artist_id`
	) as `songs_count` 
from `artists`

But this query seems pretty slow. For example, on our modest dataset of 332 artists and 1287 songs this query takes on average about 0.75 sceonds.

I tried different queries that produce the same result and these run in 0.05-0.1 seconds. Here are the queries:

select 
	artists.*,
	count(*) as songs_count
from `songs` 
	left join artists 
		on artists.id = artist_id 
group by artist_id

select 
	`artists`.*, 
    songs_count
from artists
left join 
	(select count(*) as songs_count, artist_id
     from `songs` 
     group by artist_id
    ) as `t` 
    on t.artist_id = artists.id

Could any of these queries be implemented instead of the current or are there some catches as in #18109 ? It seems that soft deletion could still be honored in these queries.

[staudenmeir]

The big advantage of the current subquery solution is the simple integration. Adding a JOIN clause is more complicated because you have to adjust the selected columns. By default, Laravel queries select all columns:

User::all(); // select * from "users"

Joining another table will override columns of the main table (laravel/ideas#347).

[sisve]

Instead of looking at sql queries, have you tried looking at the query plan? Just do EXPLAIN FORMAT=JSON SELECT ... and you'll see details about the query, like which tables and indexes will be used, and how they will be used.

What you described matches a simple case of "the first queries loaded the data from slow disks into fast memory, the second queries loaded the data from faster memory".

Also keep in mind that you changed a simple query ( SELECT a.*, ... FROM a ) into something more complex by adding the GROUP BY clause. Your example assumes that you can use the functional dependence that mysql introduced in 5.7.5. So your example will not work on mysql <5.7.5. I havn't looked into how this works if you disable strict mode, but using mysql strict mode is basically telling it that you never care about your queries returning correct data anyway...

[laurencei]

Hi there,

Welcome to Laravel and we are glad to have you as part of the community.

Unfortunately this GitHub area is not for ideas, suggestions etc. This is only for issues/bugs with the framework code itself.

I will be closing your ticket here. You are able to open a ticket at https://github.com/laravel/ideas

Alternatively you are able to open a PR using the Contributions guidelines: https://laravel.com/docs/5.7/contributions#which-branch

If you feel I've closed this issue in error, please provide more information about how this is a framework issue, and I'll reopen the ticket.

Thanks in advance.