CODEWITHSUNDEEP
mysqlsqldatabase
valeron
valeron

Reputation: 115

slow join on table (10k rows)

i have table actions (30 rows) and passed_actions(10k rows)

actions table:

CREATE TABLE `actions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `category_id` int(10) unsigned NOT NULL,
  `author_id` int(10) unsigned NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `slug` varchar(255) COLLATE utf8_unicode_ci NOT NULL COMMENT 'need for url',
  `about` longtext COLLATE utf8_unicode_ci,
  `image` text COLLATE utf8_unicode_ci,
  `page_title` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `page_description` varchar(512) COLLATE utf8_unicode_ci DEFAULT NULL,
  `active` tinyint(1) NOT NULL DEFAULT '0',
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `actions_slug_unique` (`slug`),
  KEY `actions_author_id_foreign` (`author_id`),
  KEY `actions_category_id_foreign` (`category_id`),
  CONSTRAINT `actions_author_id_foreign` FOREIGN KEY (`author_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `actions_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

and passed_actions (~9500 rows)

CREATE TABLE `passed_actions` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
  `action_id` int(10) unsigned NOT NULL,
  `hash` varchar(6) COLLATE utf8_unicode_ci NOT NULL,
  `public` tinyint(1) NOT NULL DEFAULT '1',
  `successfully_passed` tinyint(1) NOT NULL DEFAULT '0',
  `started_at` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `passed_actions_hash_unique` (`hash`),
  KEY `passed_actions_user_id_foreign` (`user_id`),
  KEY `passed_actions_action_id_foreign` (`action_id`),
  CONSTRAINT `passed_actions_action_id_foreign` FOREIGN KEY (`action_id`) REFERENCES `actions` (`id`) ON DELETE CASCADE,
  CONSTRAINT `passed_actions_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=25733 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

this query takes 0.3-0.5s:

select
    actions.*
from actions
left join passed_actions.action_id = actions.id
group by actions.id
order by count(passed_actions.id) DESC

this affects to response time of my api... why is this happening? i think that 10k rows is not a large table...

i use default mysql config. My server is 1gb ram and 1 cpu (digital ocean droplet)

Upvotes: 0

Views: 141

Answers (3)

Kerols Alfons
Kerols Alfons

Reputation: 59

1- Rebuild the index and update statistics 2- Select Only the column you want to use 3- run this query in a new query and hit "Right click" and Click on "Display Estimated Execution Plan" and view the Missing Index Details and build the required index and run the query again

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

Your query is actually reasonable fast. Sometimes a correlated subquery can help:

select a.*
from actions a
order by (select count(*) from passed_actions pa where pa.action_id = a.id) desc;

This can use the index you have defined on passed_actions(action_id).

Upvotes: 1

MandyShaw
MandyShaw

Reputation: 1156

If all you want off the second table is the count for sorting, as appears to be the case, try (untested, sorry):

select
    actions.*
from actions
left join (select action_id, count(*) as passed_count from passed_actions group by action_id) p on actions.action_id = p.action_id
order by passed_count DESC

(I can't see where tests.id is coming from, I'm afraid.)

Upvotes: 0

Related Questions