I have a table that has approximately 20 million records and I'm having a very hard time improving the performance of the following query. It's taking well over a minute to run.

SELECT  a.batch_date, a.batch_number, sum(a.gross_amt) AS gross_amount,
        (sum(a.trans_fee)*-1) as trans_fee, sum(a.net_amt) AS net_amount
    FROM  cc_detail a
    WHERE  a.company_id='1'
      AND  a.account_number='999999'
      AND  (a.batch_date BETWEEN '2015-10-01' AND '2016-01-31')
    GROUP BY  a.batch_date, a.batch_number
    ORDER BY  a.batch_date DESC, a.batch_number
    LIMIT  0, 10

Here is the table structure:

CREATE TABLE `cc_detail` ( 
    `company_id` int(11) NOT NULL, 
    `account_number` int(12) NOT NULL, 
    `location_number` int(12) NOT NULL, 
    `batch_date` date NOT NULL, 
    `batch_number` varchar(10) COLLATE utf8_unicode_ci NOT NULL, 
    `gross_amt` decimal(15,6) NOT NULL, 
    `trans_fee` decimal(15,6) NOT NULL, 
    `net_amt` decimal(15,6) NOT NULL, 
    KEY `company_id` (`company_id`), 
    KEY `account_number` (`account_number`),
    KEY `location_number` (`location_number`),  
    KEY `batch_date` (`batch_date`),
    KEY `batch_number` (`batch_number`), 
    KEY `ca` (`company_id`,`account_number`), 
    KEY `cab` (`company_id`,`account_number`,`batch_date`), 
    KEY `cb` (`company_id`,`batch_date`) USING BTREE 
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

The server has 3.5 GB RAM and is running MariaDB 10.1. One issue with this table is that it does not have a primary or unique key. Due to the source of this data not having a unique key, I can not create one (unless someone has suggestions on ho to do this safely). This table is updated multiple times a day by deleting records within a date range and inserting data within that same date range.

Here is the result of the Explain statement using the above query:

id: 1
select_type: SIMPLE
table: a
type: ref
possible_keys: company_id,account_number,batch_date,ca,cab,cb
key: ca
key_len: 8
ref: const, const
rows: 1
Extra: Using where; Using temporary; Using filesort
  • You may be over indexed considering a lot of deletes and inserts. Are the indexes fragmented? – paparazzo Jan 24 '16 at 20:31
  • Could you paste the result of ANALYZEing your query? – Juan Carlos Coto Jan 24 '16 at 22:18
  • Frisbee, how can I check the fragmentation of my indexes? Thanks. – Davy Jan 25 '16 at 1:56
  • Juan, I edited the question and included the result of the EXPLAIN for that query. Is that what you mean by the ANALYZE? – Davy Jan 25 '16 at 1:57

INDEX(a) is unnecessary when you also have INDEX(a,b). So get rid of these:

KEY `company_id` (`company_id`),
KEY `ca` (`company_id`,`account_number`), 

ca is a good index, but cab is better; removing ca will encourage the optimizer to use cab. How many rows are in the result set?

Was the SELECT slow during one of those big DELETE + INSERT times? If so, please provide those queries for further discussion.

| improve this answer | |
  • The select query above is the most common, but can vary. In some scenarios, no batch date may be given. Would it still be wise to delete index "ca"? I will check my logs to be sure the select and deletes do not run concurrently. I'm sure it does happen, but not often is my assumption. The delete query runs in a loop for each date within a 30 day range. DELETE FROM cc_detail where company_id=:company_id AND batch_date=:batch_date – Davy Jan 25 '16 at 3:27
  • cap handles any needs for ca, so ca can be deleted without loss of functionality. – Rick James Jan 25 '16 at 6:57
  • I deleted all keys with the exception of cab, batch_number and location_number. Now I cannot get MySQL to use the cab index with that same query. Test using Explain and running the query takes well over 5 minutes now. Is it possible that the GROUP BY clause of the query is preventing this from using the cab index? – Davy Jan 26 '16 at 5:33
  • I suggest writing a bug report against MariaDB - mariadb.atlassian.net/issues/… – Rick James Jan 26 '16 at 5:50
  • If 10.1 has EXPLAIN FORMAT=JSON, apply that to the SELECT and show us the result. – Rick James Jan 26 '16 at 5:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.