I have a query that is part of a larger query. I have tried two versions of the query to try to improve the page run time. One version runs almost instantaneously.. but sometimes (approx one in 50) runs extremely slowly (almost 7 minutes), the other version runs a bit slower (multiple seconds) but is consistent (i.e. doesn't run extremely slowly for some accounts)
Version 1 (slightly quick, consistently)
select max(created_at) from transactions where
created_at < date_add('2022-02-27 06:00:00',interval -60 day)
and customer_username = c.username and status = 'SUCCESS'
and category in('TICKET','DEPOSIT') and subcategory = 'RESPONSE'
Version 2 (instant, but on rare occasions VERY slow)
select created_at from transactions where
created_at < date_add('2022-02-27 06:00:00',interval -60 day)
and customer_username = c.username and status = 'SUCCESS'
and category in('TICKET','DEPOSIT') and subcategory = 'RESPONSE'
order by created_at desc limit 1
(To summarize. One uses max() to get the latest date. The other uses order by with limit 1 to get the latest date)
These are inner/sub queries within a larger query, being run on a remote database, which I have very limited granted privileges on, so I can't utilize performance improving tricks that I'm used to using on an internal MS SQl database (such as creating helpful temporary tables)
Is there any mysql trick I am not aware of that can give me the best of both worlds? These queries are part of a report where they can be run maybe a hundred times, so I'm stuck having the report either time out (or take a very long time) because of one or two VERY slow runs, or hundreds of slower-than-instant runs.
Extra information...
CREATE TABLE `transactions` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`status` varchar(10) NOT NULL,
`customer_username` varchar(128) NOT NULL,
`ticket_id` int(11) DEFAULT NULL,
`category` varchar(10) NOT NULL,
`subcategory` varchar(10) NOT NULL,
`channel` varchar(20) NOT NULL,
`provider_id` varchar(10) DEFAULT NULL,
`amount` decimal(20,8) NOT NULL DEFAULT '0.00000000',
`balance_forward` decimal(20,8) NOT NULL DEFAULT '0.00000000',
`currency` varchar(3) NOT NULL,
`ip_address` varchar(128) DEFAULT NULL,
`description` varchar(128) DEFAULT NULL,
`response_code` varchar(128) DEFAULT NULL,
`response_text` varchar(255) DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`account_id` int(11) NOT NULL,
`withdrawal_id` int(11) DEFAULT NULL,
`deposit_id` int(11) DEFAULT NULL,
`committed_at` datetime DEFAULT NULL,
`provider_reference` varchar(255) DEFAULT NULL,
`account_adjust_id` int(11) DEFAULT NULL,
`payment_card_merchant_reference` varchar(255) DEFAULT NULL,
`owner_category` varchar(255) DEFAULT 'CUSTOMER',
`merchant_reference` varchar(255) DEFAULT NULL,
`authentik_user_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `index_transactions_on_customer_username` (`customer_username`),
KEY `index_transactions_on_ticket_id` (`ticket_id`),
KEY `index_transactions_on_category_and_subcategory` (`category`,`subcategory`),
KEY `index_transactions_on_deposit_id` (`deposit_id`),
KEY `index_transactions_on_withdrawal_id` (`withdrawal_id`),
KEY `index_transactions_on_account_adjust_id` (`account_adjust_id`),
KEY `index_transactions_on_provider_reference` (`provider_reference`),
KEY `index_transactions_on_created_at` (`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=233634386 DEFAULT CHARSET=utf8;
Intent: The intent of the larger query that contains this query is to determine whether an account didn't wager between date minus 1 year and date minus 60 days (implied that they HAVE wagered or used their account within the last 60 days) This is essentially determining whether it's a reactivated account. The larger query (itself part of a query too big to include here) is below...
case when
(select created_at from transactions where
created_at < date_add('$todate',interval -60 day)
and customer_username = c.username and status = 'SUCCESS'
and category in('TICKET','DEPOSIT')
and subcategory = 'RESPONSE' order by created_at desc limit 1 )
< date_add('$todate',interval -1 year)
then 'reactivated' else 'active' end active_status
I cannot change indexes, or tables, or create temporary tables (amongst other things)
I do not know the precise database version or hardware specs.