Improve count query with join and where statement-CodePudding

I'm running a count query which is very slow, how can improve this?

I've got the following query, but it takes around 1.33 seconds:

select
count(*) as aggregate
from
`tickets`
inner join `orders` on `orders`.`id` = `tickets`.`order_id`
where
`orders`.`status` = 'paid' and
`tickets`.`created_at` > '2023-01-01 00:00:00'

The tickets table has around 650000 rows and the order table has around 320000 rows.

This is the result of SHOW CREATE TABLE tickets:

  CREATE TABLE `tickets` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `tickettype_id` int unsigned NOT NULL,
  `order_id` int unsigned NOT NULL,
  `variant_id` bigint unsigned DEFAULT NULL,
  `seat_id` bigint unsigned DEFAULT NULL,
  `barcode` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `first_name` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `last_name` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `email` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `telephone` varchar(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `birthday` date DEFAULT NULL,
  `age` int unsigned DEFAULT NULL,
  `gender` enum('m','f') CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `price` double(10,2) DEFAULT NULL,
  `extra_info` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tickets_barcode_unique` (`barcode`),
  KEY `tickets_tickettype_id_foreign` (`tickettype_id`),
  KEY `tickets_order_id_foreign` (`order_id`),
  KEY `tickets_order_id_index` (`order_id`),
  KEY `tickets_tickettype_id_index` (`tickettype_id`),
  KEY `tickets_seat_id_foreign` (`seat_id`),
  KEY `tickets_variant_id_foreign` (`variant_id`),
  CONSTRAINT `tickets_ibfk_1` FOREIGN KEY (`order_id`) REFERENCES `orders` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tickets_seat_id_foreign` FOREIGN KEY (`seat_id`) REFERENCES `seatplan_seats` (`id`) ON DELETE SET NULL,
  CONSTRAINT `tickets_tickettype_id_foreign` FOREIGN KEY (`tickettype_id`) REFERENCES `tickets_types` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tickets_variant_id_foreign` FOREIGN KEY (`variant_id`) REFERENCES `ticket_variants` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=2945088 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

How can I improve the speed?

CodePudding user response：

The performance of your query depends on several factors. Such as,

The table size
Performance of your machine
Indexing etc.

If you dont have indices created for status, order_id and created_at, better create them. Which can significantly improve the query performance.

CREATE INDEX order_id_index ON tickets(order_id);
CREATE INDEX status_index ON orders(status);
CREATE INDEX created_at_index ON tickets(created_at);

Additionally, if you are using PostgreSQL, try running VACUUM on your tables which removes the dead tuples and improves performance.

CodePudding user response：

First of all

You need to add two indexes :

CREATE INDEX order_id_idx ON tickets(order_id);

// composite index since you are using both columns in where
CREATE INDEX status_created_at_idx ON tickets(status, created_at);

The query optimizer uses the composite indexes for queries that test all columns in the index, or queries that test the first columns, the first two columns, and so on.

More informations regarding composite can be found here