CODEWITHSUNDEEP
mysqlsqlmariadb
James Spittal
James Spittal

Reputation: 237

Simple SELECT query is slow with ~4Gb table (MariaDB)

I have a table called tablename with the following structure:

 CREATE TABLE `tablename` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `type` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `data` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tablename_user_id_foreign` (`user_id`),
  CONSTRAINT `tablename_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=11271577 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

The current size of this table is 4.1Gb currently and I expect it to get larger.

I'm running a fairly simple SQL SELECT query which looks like:

SELECT `type`, `data`, `created_at`
FROM `tablename`
WHERE `type` = 'PURCHASE'
ORDER BY `created_at` DESC
LIMIT 1

Essentially, we're looking to return only one (and no more) of the most recent rows in this DB (of type PURCHASE). Most data in this table is not of type PURCHASE.

On a production database with 4gb of RAM, this query can be seen to take as long as 1 minute and 1 second (which seems way too long).

SELECT `type`, `data`, `created_at`
FROM `tablename`
WHERE `type` = 'PURCHASE'
ORDER BY `created_at` DESC
LIMIT 1;
/* Affected rows: 0  Found rows: 1  Warnings: 0  Duration for 1 query: 00:01:01.8 */

On other runs (same box, same server), I've seen it take between 27 seconds and 36.48 seconds.

I'd like to see this down to a couple seconds at most if possible.

The version of MariaDB is 10.3.27 (running on *nix virtual cloud instance with 4Gb RAM and "General Purpose (SSD) volumes"). Is this normal? What can be done to speed up this query? Would the bottleneck be mostly associated with RAM or disk speed or something else?

It may be worth noting that a lot of new records are being written/inserted into this same table at the same time this SELECT statement is likely to be executing, so this could be slowing things down (waiting for INSERT statements to finish perhaps).

Upvotes: 2

Views: 1229

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

For this query:

SELECT `type`, `data`, `created_at`
FROM `tablename`
WHERE `type` = 'PURCHASE'
ORDER BY `created_at` DESC
LIMIT 1;

You want an index on tablename(type, created_at desc).

Upvotes: 2

Related Questions