Slow MySQL with join query written in Laravel/Eloquent for Yajra DataTables - Phpmyadmin

KristjanO
March 23, 2021
269 views
0 votes
2 Answers

I am trying to pull the data from two tables by joining the data, but the sql query executed is dead slow. The idea is to pull all users and then combine with the created_at date from points table. Pulling all users or all points is rather quick, but having problems with writing a proper join sql. I did try to add indexes to appropriate columns (points.created_at for example), but those made query even slower.

This is the code that generates the query:

return $this->user
            ->query()
            ->select(['users.id', 'users.email', 'users.role', 'users.created_at', 'users.updated_at', 'pt.created_at AS last_transaction'])
            ->leftJoin(DB::raw('(SELECT points.user_id, points.created_at FROM points ORDER BY points.created_at DESC) AS pt'), 'pt.user_id', '=', 'users.id')
            ->where('users.role', 'consument')
            ->groupBy('users.id');

Which generates this query:

select `users`.`id`, `users`.`email`, `users`.`role`, `users`.`created_at`, `users`.`updated_at`, `pt`.`created_at` as `last_transaction`
from `users`
left join (SELECT points.user_id, points.created_at FROM points ORDER BY points.created_at DESC) AS pt on `pt`.`user_id` = `users`.`id`
where `users`.`role` = ? and `users`.`deleted_at` is null
group by `users`.`id`
order by `id` asc

Users table:

CREATE TABLE `users` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `email` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `password` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `remember_token` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
  `role` varchar(15) COLLATE utf8_unicode_ci DEFAULT 'consument',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `deleted_at` timestamp NULL DEFAULT NULL,
  `email_verified_at` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `email_verify_token` text COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=84345 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Points table:

CREATE TABLE `points` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `tablet_id` int(10) unsigned DEFAULT NULL,
  `parent_company` int(10) unsigned NOT NULL,
  `company_id` int(10) unsigned NOT NULL,
  `points` int(10) unsigned NOT NULL,
  `mutation_type` tinyint(3) unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `points_user_id_foreign` (`user_id`),
  KEY `points_company_id_foreign` (`company_id`),
  KEY `points_parent_company_index` (`parent_company`),
  KEY `points_tablet_id_index` (`tablet_id`),
  KEY `points_mutation_type_company_id_created_at_index` (`mutation_type`,`company_id`,`created_at`),
  KEY `created_at_user_id` (`created_at`,`user_id`),
  CONSTRAINT `points_company_id_foreign` FOREIGN KEY (`company_id`) REFERENCES `companies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `points_parent_company_foreign` FOREIGN KEY (`parent_company`) REFERENCES `parent_company` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `points_tablet_id_foreign` FOREIGN KEY (`tablet_id`) REFERENCES `tablets` (`id`) ON DELETE SET NULL ON UPDATE CASCADE,
  CONSTRAINT `points_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1798627 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Users has 84,263 and points has 1,636,119 rows. If I execute the query manually through phpMyAdmin, it takes about 150 seconds to execute. if I ran it through Laravel, page times out after 180 seconds.

I can add or remove indexes and change the sql query, but I can’t change the database structure, so any help with optimized sql query would be greatly appreciated.

Answers

If there is is only one row per user in the points table you do the following:

If there is users which does not have a post in points you could use:

select `users`.`id`, `users`.`email`, `users`.`role`, `users`.`created_at`, 
`users`.`updated_at`, `pt`.`created_at` as `last_transaction`
from `users`
left join points AS pt on `pt`.`user_id` = `users`.`id`
where `users`.`role` = ? and `users`.`deleted_at` is null
order by `id` ASC

If every user in the user table always has one post in the points table you could skip the left join and just:

select `users`.`id`, `users`.`email`, `users`.`role`, `users`.`created_at`, 
`users`.`updated_at`, `pt`.`created_at` as `last_transaction`
from `users`
join points AS pt on `pt`.`user_id` = `users`.`id`
where `users`.`role` = ? and `users`.`deleted_at` is null
order by `id` asc

This will return just one user and the latest point row based on the points column ‘created_at’.

SELECT  `u`.`id`, 
        `u`.`email`, 
        `u`.`role`, 
        `u`.`created_at`,
        `u`.`updated_at`, 
        `pt`.`created_at` as `last_transaction`
from `users` u
LEFT join points AS pt on `pt`.`user_id` = `u`.`id`
LEFT JOIN (
    SELECT user_id, MAX(created_at) AS mm FROM points GROUP BY user_id
) AS m ON m.user_id = pt.user_id
where `u`.`role` = ? and `u`.`deleted_at` is NULL AND m.mm = pt.created_at
order by `id` ASC;

Please signup or login to give your own answer.

Click here to cancel reply.

Slow MySQL with join query written in Laravel/Eloquent for Yajra DataTables – Phpmyadmin

Answers