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



  • 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.



  • 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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2