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.