-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;
4
  • 1
  • No. I'm not using count * and I have an index which I thought was supposed to speed up the count process due to how the data is stored. I've read the articles around the counting but people are generally talking about rows in the millions. I've got 25k which isn't many in comparison. Almost didn't post on here as I knew I'd just get downvoted ... I've provided very clear info and the question quality is good. Still a downvote.
    – webnoob
    Aug 5 at 17:11
  • Do show engine innodb status before and after running the query and see what it's doing.
    – mustaccio
    Aug 5 at 20:12
  • Another example of why stack exchange is going down the toilet... This question was asked in a very clear way and still gets downvoted and closed. The suggested duplicate post is very different and I specified the reasons why.
    – webnoob
    Aug 8 at 8:23
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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.