Counting relationships performance

Can we improve the performance of many-to-many text column counting relationships?

Tables\Columns\TextColumn::make('users_count')->counts('users');

Tables\Columns\TextColumn::make('users_count')->counts('users');

This code executes the SQL query below, which takes approximately 22 seconds to complete:

SELECT `notifications`.*,
  (
    SELECT count(*)
    FROM `users`
      inner join `notification_user` on `users`.`id` = `notification_user`.`user_id`
    WHERE `notifications`.`id` = `notification_user`.`notification_id`
  ) as `users_count`
FROM `notifications`
ORDER BY `created_at` DESC
LIMIT 10 offset 0;

SELECT `notifications`.*,
  (
    SELECT count(*)
    FROM `users`
      inner join `notification_user` on `users`.`id` = `notification_user`.`user_id`
    WHERE `notifications`.`id` = `notification_user`.`notification_id`
  ) as `users_count`
FROM `notifications`
ORDER BY `created_at` DESC
LIMIT 10 offset 0;

Can Filament enhance the query's performance? The custom query below currently takes approximately 3 seconds to execute on a table with over 1 million rows.

SELECT
  `notifications`.*, 
  COUNT(DISTINCT `notification_user`.`user_id`) AS `users_count`
FROM `notifications`
LEFT JOIN `notification_user` ON `notification_user`.`notification_id` = `notifications`.`id`
GROUP BY `notifications`.`id`
ORDER BY `notifications`.`created_at` DESC
LIMIT 10 
OFFSET 0;

SELECT
  `notifications`.*, 
  COUNT(DISTINCT `notification_user`.`user_id`) AS `users_count`
FROM `notifications`
LEFT JOIN `notification_user` ON `notification_user`.`notification_id` = `notifications`.`id`
GROUP BY `notifications`.`id`
ORDER BY `notifications`.`created_at` DESC
LIMIT 10 
OFFSET 0;

0 Replies

No replies yetBe the first to reply to this messageJoin

Gaming

Programming

Counting relationships performance