Mysql query that is very quick (but sometimes very slow) versus an alternative version of the same query that is consistently a *bit* slow

Question

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.

Answer 1

I (seem to have) fixed this by adding a query to check the creation date of the account.

and created_at >= (select created_at from customers where username = '__username__' )

(username is string_replaced in php with the actual username. I did try username = c.username first but that didn't acheive the performance improvement so I resorted to the php string replace (Note, this is not user-supplied info, so not a sql injection risk)

select created_at from transactions where 
created_at < date_add('2022-02-27 06:00:00',interval -60 day)

/* Added this line... */
and created_at >= (select created_at from customers where username = '__username__' )

and customer_username = '__username__' and status = 'SUCCESS' 
and category in('TICKET','DEPOSIT') and subcategory = 'RESPONSE'
order by created_at desc limit 1

It was performing very slow for accounts that hadn't actually been created in the date range being queried, so the very slow query was coming back NULL

Answer 2

Simplification (but no speed difference):

date_add('2022-02-27 06:00:00',interval -60 day)

-->

'2022-02-27 - INTERVAL 60 DAY

Is this just a subquery? And the outer query involves a table with alias c? Then, this should be improved index:

INDEX(customer_name, status, subcategory, category, created_at)

And drop your current KEY(customer_name) since this handles its needs.

You tagged it [mysql], but mentioned "MS SQl"?? MySQL can create temp tables, but I don't see any benefit.

The case does not match the two queries at the top, so I am confused as to what I should be investigating.

A major part of the performance variation is that there is an IN (a la OR) with more than one value. Normally turning it into a UNION can help this OR, but I am not sure about this case.

Are there 233M rows in the table? Is the table about 120GB on disk (data+index)? (SHOW TABLE STATUS) I see lots of possible places to shrink datatypes; doing so might cut back significantly on the size, thereby improving the cachability, hence speed. How much RAM do you have? What is the setting of innodb_buffer_pool_size?

Normalizing status, category, subcategory, channel, and a few others, would shrink from maybe an average of 10 bytes each down to 1 or 2 (TINYINT UNSIGNED, 0-255 or SMALLINT UNSIGNED 0-64K).

Another point. Since my index is "covering", it the subquery would be contained to the size of just the index. This would avoid what is happening now -- namely scan of most of the 100GB of data, replacing it by maybe only 5MB of index.

True, I have not addressed the question about the two formulations running differently. Instead, I am suggesting changes to make that question irrelevant. The "7 minutes" should drop to "several seconds", while some variations may drop to "virtually instant".

And, assuming the data will continue to grow, this (and other) performance problems will continue to get worse.

Answer 3

This concept might work for you. From

select created_at from transactions where 
created_at < date_add('2022-02-27 06:00:00',interval -60 day) 

to

SET @qcdate=0;SELECT date_add('2022-02-27 06:00:00',interval -60 day) INTO @qcdate; 

select created_at from transactions where 
created_at < @qcdate

where you have calculated the date and eliminated the non-deterministic situation you have today. This avoids recalculating the date for each considered row - imho. No changes at host required.