Pagination/Count sqls with union are really slow

[parallels999]

Current SQL looks like this

select count(*) as aggregate from (
    (select * from `audits_1`) 
    union (select * from `audits_2`)
) as `temp_table`; /* 64.21ms */

How about querying each sql individually by union for avoid getting all the columns before the count?

select count(*) as aggregate from `audits_1`; /* 5.29ms */
select count(*) as aggregate from `audits_2`; /* 5.76ms */

Sow now we can accumulate results (count_0 + count_1 + count_n)

Also

select (
     select count(*) as aggregate from `audits_1`
     + select count(*) as aggregate from `audits_2`
) as aggregate; /* 6.16ms */

framework/src/Illuminate/Database/Query/Builder.php

Lines 2789 to 2794 in df696e9

	$without = $this->unions ? ['orders', 'limit', 'offset'] : ['columns', 'orders', 'limit', 'offset'];
	return $this->cloneWithout($without)
	->cloneWithoutBindings($this->unions ? ['order'] : ['select', 'order'])
	->setAggregate('count', $this->withoutSelectAliases($columns))
	->get()->all();

[ImBIOS]

$audits_1_count = DB::table('audits_1')->count();
$audits_2_count = DB::table('audits_2')->count();

$total_count = $audits_1_count + $audits_2_count;

$result = DB::table(DB::raw(''))
             ->selectRaw('(?) as aggregate', [$total_count])
             ->get();

@parallels999 Would not the above Laravel code is similar to:

select (
     select count(*) as aggregate from `audits_1`
     + select count(*) as aggregate from `audits_2`
) as aggregate;

I wonder if it does faster tho. How can I measure the processing time of the above code?

[parallels999]

I'm using laravel debugbar, it show times of every sql

[ImBIOS]

@parallels999 Can you share your project specific to this issue so we'll quickly reproduce it and determine the best solution?
It'd be awesome if we could make Laravel even faster and more reliable. I love 💝 your finding.