-1

EDIT: I'm not asking about count(*) which won't take indexes into account, I'm also not asking about the difference between actual counts on table vs information_scema as noted in the suggested duplicate.

I have a table with 25022 rows in.

Running select count(userId) from penName takes 15.921 seconds.

I'm a little confused as 25k rows isn't many so not sure what else to look at given I have an index on the table.

Explain is showing it's using the index:

enter image description here

Why is the count so slow?

The table is this:

select count(id) from penName;     
     CREATE TABLE `penname` (
  `id` int NOT NULL AUTO_INCREMENT,
  `createdDate` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `isDraft` tinyint(1) NOT NULL DEFAULT '1',
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `followerCount` int NOT NULL DEFAULT '0',
  `firstName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `lastName` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `dmFirst1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `dmFirst2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `dmLast1` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `dmLast2` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `updatedDate` datetime(3) DEFAULT NULL,
  `bio` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci,
  `image` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `facebookLink` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `bookbubLink` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `twitterLink` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `userId` int NOT NULL,
  `instagramLink` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `penName_id_userId` (`id`,`userId`),
  KEY `userId` (`userId`),
  CONSTRAINT `penname_ibfk_1` FOREIGN KEY (`userId`) REFERENCES `user` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=25023 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
webnoob
  • 605
  • 2
  • 6
  • 18

1 Answers1

1

Turns out the innodb_buffer_pool_size was set to 8MB on my default windows install of MySQL.

Increasing this limit has improved the query speed exponentially.

webnoob
  • 605
  • 2
  • 6
  • 18