app/Exports/VarianceInventoryLinesExport.php

Nice, that helps a lot. With the full buildData() we can see all the queries that need to be supported by indexes.

Short answer:
It’s not just the inventory_masters(barcode) query – there are a few missing composite indexes upstream that will also burn CPU, plus a small fix needed in the barcode whereIn.

⸻

1. Query 1 – inventory_lines

$expected = DB::table('inventory_lines')
    ->where('inventory_session_warehouse_id', $this->inventorySessionWarehouseId)
    ->select('barcode', DB::raw('SUM(qty) as expected_qty'), 'cost', 'is_created')
    ->groupBy('barcode', 'cost', 'is_created')
    ->get()
    ->keyBy('barcode');

This will be fast only if MySQL can filter by inventory_session_warehouse_id efficiently.

✅ Recommended index

CREATE INDEX inventory_lines_session_barcode_index
    ON inventory_lines (inventory_session_warehouse_id, barcode);

Why:
	•	WHERE inventory_session_warehouse_id = ? is your filter.
	•	You then group by barcode (plus cost, is_created), so having barcode after session in the index helps a lot.

⸻

2. Query 2 – inventory_line_counts + joins

$counted = DB::table('inventory_line_counts as ilc')
    ->join('inventory_lines as il', 'il.id', '=', 'ilc.inventory_line_id')
    ->join('inventory_zones as z', 'ilc.inventory_zone_id', '=', 'z.id')
    ->where('z.inventory_session_warehouse_id', $this->inventorySessionWarehouseId)
    ->select(
        'il.barcode',
        DB::raw('SUM(ilc.counted_qty) as counted_qty'),
        DB::raw('GROUP_CONCAT(DISTINCT CONCAT(z.prefix, z.number) ORDER BY z.number ASC SEPARATOR ", ") as zones')
    )
    ->groupBy('il.barcode')
    ->get()
    ->keyBy('barcode');

This one can be very heavy if the join columns aren’t indexed.

✅ Must-have indexes
	1.	On inventory_zones:

CREATE INDEX inventory_zones_session_index
    ON inventory_zones (inventory_session_warehouse_id);

	2.	On inventory_line_counts:

CREATE INDEX inventory_line_counts_line_index
    ON inventory_line_counts (inventory_line_id);

CREATE INDEX inventory_line_counts_zone_index
    ON inventory_line_counts (inventory_zone_id);

If this table is very big, a composite can be even better:

CREATE INDEX inventory_line_counts_zone_line_index
    ON inventory_line_counts (inventory_zone_id, inventory_line_id);

	3.	On inventory_lines you already have PK on id, so the join il.id = ilc.inventory_line_id uses that.

⸻

3. Query 3 – inventory_masters with whereIn(barcode, …)

$descriptions = DB::table('inventory_masters')
    ->select('barcode', 'description', 'codein', 'colour', 'size', 'style')
    ->whereIn('barcode', $expected->keys()->merge($counted->keys())->unique())
    ->get()
    ->keyBy('barcode');

Index you need (and you said it already exists):

CREATE INDEX inventory_masters_barcode_index
    ON inventory_masters (barcode);

So what’s hurting here?

🔥 The real problem: dirty / mixed barcode values

The keys you’re feeding to whereIn likely contain:
	•	Numbers (100000000100, 167642773673, etc.)
	•	Strings with leading/trailing spaces (' 02442202 ', ' FN3268-010-XS-L- ')
	•	Alphanumeric strings (fine, but still need to be clean)

If inventory_masters.barcode is VARCHAR, and your IN list has numeric literals, MySQL will do type conversion and can stop using the index efficiently → high CPU.

✅ Fix in your PHP

Clean and normalize barcodes once, then reuse:

protected function buildData()
{
    // 1. Expected
    $expected = DB::table('inventory_lines')
        ->where('inventory_session_warehouse_id', $this->inventorySessionWarehouseId)
        ->select('barcode', DB::raw('SUM(qty) as expected_qty'), 'cost', 'is_created')
        ->groupBy('barcode', 'cost', 'is_created')
        ->get()
        ->map(function ($row) {
            $row->barcode = trim((string) $row->barcode);
            return $row;
        })
        ->keyBy('barcode');

    // 2. Counted
    $counted = DB::table('inventory_line_counts as ilc')
        ->join('inventory_lines as il', 'il.id', '=', 'ilc.inventory_line_id')
        ->join('inventory_zones as z', 'ilc.inventory_zone_id', '=', 'z.id')
        ->where('z.inventory_session_warehouse_id', $this->inventorySessionWarehouseId)
        ->select(
            'il.barcode',
            DB::raw('SUM(ilc.counted_qty) as counted_qty'),
            DB::raw('GROUP_CONCAT(DISTINCT CONCAT(z.prefix, z.number) ORDER BY z.number ASC SEPARATOR ", ") as zones')
        )
        ->groupBy('il.barcode')
        ->get()
        ->map(function ($row) {
            $row->barcode = trim((string) $row->barcode);
            return $row;
        })
        ->keyBy('barcode');

    // 3. Unified, normalized barcode list
    $barcodes = $expected->keys()
        ->merge($counted->keys())
        ->filter()                                  // drop null/empty
        ->map(fn ($v) => trim((string) $v))         // ensure strings
        ->unique()
        ->values()
        ->all();                                    // whereIn likes plain array

    // 4. Descriptions
    $descriptions = DB::table('inventory_masters')
        ->select('barcode', 'description', 'codein', 'colour', 'size', 'style')
        ->whereIn('barcode', $barcodes)
        ->get()
        ->map(function ($row) {
            $row->barcode = trim((string) $row->barcode);
            return $row;
        })
        ->keyBy('barcode');

    // 5. Combine rows with mismatches only
    return collect($barcodes)
        ->filter(function ($barcode) use ($expected, $counted) {
            $e = $expected[$barcode]->expected_qty ?? 0;
            $c = $counted[$barcode]->counted_qty ?? 0;
            return $e != $c;
        })->map(function ($barcode) use ($expected, $counted, $descriptions) {
            $e = $expected[$barcode] ?? null;
            $c = $counted[$barcode]->counted_qty ?? 0;
            $zones = $counted[$barcode]->zones ?? '-';
            $desc = $descriptions[$barcode] ?? (object) [];

            return (object) [
                'barcode'      => $barcode,
                'description'  => $desc->description ?? '',
                'codein'       => $desc->codein ?? '',
                'colour'       => $desc->colour ?? '',
                'size'         => $desc->size ?? '',
                'style'        => $desc->style ?? '',
                'is_created'   => $e?->is_created ?? 0,
                'cost'         => $e?->cost ?? 0,
                'expected_qty' => $e?->expected_qty ?? 0,
                'counted_qty'  => $c,
                'zones'        => $zones,
            ];
        });
}

Key points:
	•	Normalize barcode in all three datasets (expected, counted, descriptions).
	•	Use a single $barcodes collection for both:
	•	whereIn('barcode', $barcodes)
	•	the final loop/map.

That means the barcodes hitting MySQL are clean strings, and they match the index type → the barcode index can actually be used efficiently.

⸻

So, which indexes are “missing”?

Given your code, I’d say these are the ones you should add if they’re not already there:

-- 1) For inventory_lines (Query 1)
CREATE INDEX inventory_lines_session_barcode_index
    ON inventory_lines (inventory_session_warehouse_id, barcode);

-- 2) For inventory_zones (Query 2)
CREATE INDEX inventory_zones_session_index
    ON inventory_zones (inventory_session_warehouse_id);

-- 3) For inventory_line_counts (Query 2)
CREATE INDEX inventory_line_counts_zone_line_index
    ON inventory_line_counts (inventory_zone_id, inventory_line_id);

-- 4) Already there, but listing for completeness (Query 3)
CREATE INDEX inventory_masters_barcode_index
    ON inventory_masters (barcode);

If you add those and normalize barcodes as above, CPU usage for this report should drop a lot.

If you want, you can paste the EXPLAIN output for those queries and I can sanity-check that MySQL is actually picking the right indexes.