0

I have 2 mysql servers (master and slave). I've enabled InnoDB compression on Slave, after that mysql sometimes chooses wrong index on query.

Explain on Master:


+----+-------------+-------+--------+---------------+---------+---------+-----------------------------+-----------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                         | rows      | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+-----------------------------+-----------+-------------+
|  1 | SIMPLE      | p     | range  | PRIMARY       | PRIMARY | 8       | NULL                        | 112017572 | Using where |
|  1 | SIMPLE      | l     | eq_ref | PRIMARY       | PRIMARY | 8       |            p.loan_ID        |         1 | NULL        |
|  1 | SIMPLE      | af    | eq_ref | PRIMARY       | PRIMARY | 8       |            p.fromAccount_ID |         1 | Using where |
|  1 | SIMPLE      | at    | eq_ref | PRIMARY       | PRIMARY | 8       |            p.toAccount_ID   |         1 | Using where |
+----+-------------+-------+--------+---------------+---------+---------+-----------------------------+-----------+-------------+

Explain on Slave:


+----+-------------+-------+--------+-------------------------------------------------------------------------------+--------------------+---------+-----------------------------+--------+----------------------------------------------+
| id | select_type | table | type   | possible_keys                                                                 | key                | key_len | ref                         | rows   | Extra                                        |
+----+-------------+-------+--------+-------------------------------------------------------------------------------+--------------------+---------+-----------------------------+--------+----------------------------------------------+
|  1 | SIMPLE      | l     | index  | PRIMARY                                                                       | FK243910AAD869E6   | 9       | NULL                        | 804876 | Using index; Using temporary; Using filesort |
|  1 | SIMPLE      | p     | ref    | PRIMARY,FK4BE7532292C5D482,FK4BE75322AE503A13,FK4BE75322382D11BC,POSTING_DATE | FK4BE75322382D11BC | 9       |            l.ID             |    101 | Using index condition; Using where           |
|  1 | SIMPLE      | af    | eq_ref | PRIMARY                                                                       | PRIMARY            | 8       |            p.fromAccount_ID |      1 | Using where                                  |
|  1 | SIMPLE      | at    | eq_ref | PRIMARY                                                                       | PRIMARY            | 8       |            p.toAccount_ID   |      1 | Using where                                  |
+----+-------------+-------+--------+-------------------------------------------------------------------------------+--------------------+---------+-----------------------------+--------+----------------------------------------------+

SELECT 
      p.ID AS 'payment_id',
      p.loan_ID AS 'loan_id',
      l.client_ID AS 'client_ID',
      p.amount AS 'amount',
      p.postingDate AS 'payment_date',
             CASE 
                 WHEN af.acc_type = 'POLCH' THEN 'wallet'    
                 WHEN af.acc_type = 'PLTCH' THEN 'wallet'        
                 WHEN af.acc_type = 'CNTT' THEN 'bank'        
                 WHEN af.acc_type = 'CNT2' THEN 'bank'        
                 WHEN af.acc_type = 'KONCH' THEN 'bank'        
                 WHEN af.acc_type = 'KRDTM' THEN 'cash'        
                 WHEN af.acc_type = 'LDRCH' THEN 'bank'        
                 ELSE concat('UNKNOWN_',af.acc_type) 
                 END AS 'payment_system_type', 
      af.description AS 'payment_system' 
      FROM Posting AS p 
      INNER JOIN Account AS af ON p.fromAccount_ID = af.ID 
      INNER JOIN Account AS at ON p.toAccount_ID = at.ID 
      INNER JOIN Loan AS l ON p.loan_id = l.ID 
      WHERE ( 
             af.acc_type = 'KONCH' 
             OR af.acc_type = 'PLTCH' 
             OR af.acc_type = 'POLCH' 
             OR af.acc_type = 'KRDTM' 
             OR  af.acc_type = 'LDRCH' 
             OR af.acc_type = 'CNT2' 
             OR af.acc_type = 'CNTT') 
             AND at.acc_type = 'ABON' 
             AND p.postingDate < DATE(now()) 
             AND p.ID > 0 
ORDER BY p.ID LIMIT 10000;

Loan - l

Posting - P

Master:

| Loan  | CREATE TABLE `Loan` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `amount` decimal(19,4) DEFAULT NULL,
  `amountToReturn` decimal(19,4) DEFAULT NULL,
  `isGivenOut` bit(1) DEFAULT b'0',
  `isPaid` bit(1) DEFAULT NULL,
  `issueDate` datetime DEFAULT NULL,
  `loanPeriod` int(11) DEFAULT NULL,
  `productType` varchar(255) DEFAULT NULL,
  `realPayDate` datetime DEFAULT NULL,
  `client_ID` bigint(20) DEFAULT NULL,
  `product_ID` bigint(20) DEFAULT NULL,
  `givenOutDate` datetime DEFAULT NULL,
  `isPaidByBank` bit(1) DEFAULT NULL,
  `accountNumberNBKI` varchar(255) DEFAULT NULL,
  `needManualProcessing` bit(1) DEFAULT NULL,
  `isReverted` bit(1) DEFAULT b'0',
  `showInNBCHReport` bit(1) DEFAULT b'1',
  `stake` decimal(19,5) DEFAULT NULL,
  `ignoreProlongation` bit(1) DEFAULT b'0',
  `stakeAfter21` decimal(19,5) DEFAULT NULL,
  `discount_id` bigint(20) DEFAULT NULL,
  `showInEquifaxReport` bit(1) DEFAULT b'1',
  `ignoreNbch` bit(1) DEFAULT b'0',
  PRIMARY KEY (`ID`),
  KEY `FK2439106EC0BA18` (`product_ID`),
  KEY `ISPAID_INDEX` (`isPaid`) USING BTREE,
  KEY `ISP_ISGOUT_INDEX` (`isPaid`,`isGivenOut`),
  KEY `ISSUEDATE_INDEX` (`issueDate`),
  KEY `FK243910735827C6` (`discount_id`),
  KEY `idx_Loan_realPayDate` (`realPayDate`),
  KEY `idx_Loan_givenOutDate` (`givenOutDate`),
  KEY `FK243910AAD869E6` (`client_ID`),
  CONSTRAINT `_FK243910735827C6` FOREIGN KEY (`discount_id`) REFERENCES `Discount` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2000623399 DEFAULT CHARSET=utf8 

 Posting | CREATE TABLE `Posting` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `amount` decimal(19,4) DEFAULT NULL,
  `postingDate` datetime DEFAULT NULL,
  `fromAccount_ID` bigint(20) DEFAULT NULL,
  `loan_ID` bigint(20) DEFAULT NULL,
  `toAccount_ID` bigint(20) DEFAULT NULL,
  `sourceType` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `FK4BE7532292C5D482` (`fromAccount_ID`),
  KEY `FK4BE75322AE503A13` (`toAccount_ID`),
  KEY `FK4BE75322382D11BC` (`loan_ID`),
  KEY `POSTING_DATE` (`postingDate`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=230996702 DEFAULT CHARSET=utf8

Slave:

| Loan  | CREATE TABLE `Loan` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `amount` decimal(19,4) DEFAULT NULL,
  `amountToReturn` decimal(19,4) DEFAULT NULL,
  `isGivenOut` bit(1) DEFAULT b'0',
  `isPaid` bit(1) DEFAULT NULL,
  `issueDate` datetime DEFAULT NULL,
  `loanPeriod` int(11) DEFAULT NULL,
  `productType` varchar(255) DEFAULT NULL,
  `realPayDate` datetime DEFAULT NULL,
  `client_ID` bigint(20) DEFAULT NULL,
  `product_ID` bigint(20) DEFAULT NULL,
  `givenOutDate` datetime DEFAULT NULL,
  `isPaidByBank` bit(1) DEFAULT NULL,
  `accountNumberNBKI` varchar(255) DEFAULT NULL,
  `needManualProcessing` bit(1) DEFAULT NULL,
  `isReverted` bit(1) DEFAULT b'0',
  `showInNBCHReport` bit(1) DEFAULT b'1',
  `stake` decimal(19,5) DEFAULT NULL,
  `ignoreProlongation` bit(1) DEFAULT b'0',
  `stakeAfter21` decimal(19,5) DEFAULT NULL,
  `discount_id` bigint(20) DEFAULT NULL,
  `showInEquifaxReport` bit(1) DEFAULT b'1',
  `ignoreNbch` bit(1) DEFAULT b'0',
  PRIMARY KEY (`ID`),
  KEY `FK2439106EC0BA18` (`product_ID`),
  KEY `ISPAID_INDEX` (`isPaid`) USING BTREE,
  KEY `ISP_ISGOUT_INDEX` (`isPaid`,`isGivenOut`),
  KEY `ISSUEDATE_INDEX` (`issueDate`),
  KEY `FK243910735827C6` (`discount_id`),
  KEY `idx_Loan_realPayDate` (`realPayDate`),
  KEY `idx_Loan_givenOutDate` (`givenOutDate`),
  KEY `FK243910AAD869E6` (`client_ID`),
  CONSTRAINT `_FK243910735827C6` FOREIGN KEY (`discount_id`) REFERENCES `Discount` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2000623399 DEFAULT CHARSET=utf8 
ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4 

 Posting | CREATE TABLE `Posting` (
  `ID` bigint(20) NOT NULL AUTO_INCREMENT,
  `amount` decimal(19,4) DEFAULT NULL,
  `postingDate` datetime DEFAULT NULL,
  `fromAccount_ID` bigint(20) DEFAULT NULL,
  `loan_ID` bigint(20) DEFAULT NULL,
  `toAccount_ID` bigint(20) DEFAULT NULL,
  `sourceType` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `FK4BE7532292C5D482` (`fromAccount_ID`),
  KEY `FK4BE75322AE503A13` (`toAccount_ID`),
  KEY `FK4BE75322382D11BC` (`loan_ID`),
  KEY `POSTING_DATE` (`postingDate`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=230996702 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=4

If remove table Loan from Query

+----+-------------+-------+--------+------------------------------------------------------------+---------+---------+-----------------------------+-----------+-------------+
| id | select_type | table | type   | possible_keys                                              | key     | key_len | ref                         | rows      | Extra       |
+----+-------------+-------+--------+------------------------------------------------------------+---------+---------+-----------------------------+-----------+-------------+
|  1 | SIMPLE      | p     | range  | PRIMARY,FK4BE7532292C5D482,FK4BE75322AE503A13,POSTING_DATE | PRIMARY | 8       | NULL                        | 107736559 | Using where |
|  1 | SIMPLE      | af    | eq_ref | PRIMARY                                                    | PRIMARY | 8       | smsfinance.p.fromAccount_ID |         1 | Using where |
|  1 | SIMPLE      | at    | eq_ref | PRIMARY                                                    | PRIMARY | 8       | smsfinance.p.toAccount_ID   |         1 | Using where |
+----+-------------+-------+--------+------------------------------------------------------------+---------+---------+-----------------------------+-----------+-------------+

If I add create index acc on Account(acc_type);

Plan:

+----+-------------+-------+--------+-------------------------------------------------------------------------------+--------------------+---------+---------------------------+------+--------------------------------------------------------+
| id | select_type | table | type   | possible_keys                                                                 | key                | key_len | ref                       | rows | Extra                                                  |
+----+-------------+-------+--------+-------------------------------------------------------------------------------+--------------------+---------+---------------------------+------+--------------------------------------------------------+
|  1 | SIMPLE      | af    | range  | PRIMARY,acc                                                                   | acc                | 21      | NULL                      | 4192 | Using index condition; Using temporary; Using filesort |
|  1 | SIMPLE      | p     | ref    | PRIMARY,FK4BE7532292C5D482,FK4BE75322AE503A13,FK4BE75322382D11BC,POSTING_DATE | FK4BE7532292C5D482 | 9       | smsfinance.af.ID          |   54 | Using index condition; Using where                     |
|  1 | SIMPLE      | l     | eq_ref | PRIMARY                                                                       | PRIMARY            | 8       | smsfinance.p.loan_ID      |    1 | NULL                                                   |
|  1 | SIMPLE      | at    | eq_ref | PRIMARY,acc                                                                   | PRIMARY            | 8       | smsfinance.p.toAccount_ID |    1 | Using where                                            |
+----+-------------+-------+--------+-------------------------------------------------------------------------------+--------------------+---------+---------------------------+------+--------------------------------------------------------+

Query execute long time.

Improve this question
2
  • There are a lot of reasons why this might happen. Please provide SHOW CREATE TABLE for l and p, plus the query in question. I doubt if compression is relevant. What version(s) are the mysqls? Commented Jan 26, 2017 at 6:41
  • Edit main post. Commented Jan 26, 2017 at 8:59

1 Answer 1

Reset to default
0

I suspect compression leads to different statistics, which can lead to different execution plans.

I see no use for the table Loan in this query. Removing it from the query may force the explain plans to be the same.

You have 200M rows in each table? Another speedup would be to shrink the tables.

  • Change BIGINT (8 bytes each) to INT UNSIGNED (4 bytes, range 0..4 billion) wherever possible.
  • Normalize the _type columns, replacing with SMALLINT UNSIGNED (2 bytes, range 0..64K) or other suitable integer type. Or turn the column into an ENUM if there are a finite, small number, of "types".

Does Account have INDEX(acc_type)? (Or at least starting with acc_type.)

Remove INNER JOIN Loan AS l ON p.loan_id = l.ID and replace l.client_ID AS 'client_ID', with

( SELECT client_ID FROM Loans WHERE ID = p.loan_id ) AS 'client_ID',

I think this will force a different query plan, perhaps a good one.

Improve this answer
Sign up to request clarification or add additional context in comments.

6 Comments

Sorry, I've made mistake in query. Now tables Loan exist in query.
No, Account has index with acc_type and client_ID' UNIQUE KEY idx_unique_client_acc_type (client_ID,acc_type)'
I need an index starting with acc_type.
See my added suggestion for dealing with Loan.
You cannot directly prevent the change without put serious downside risk. Here's another thing that might help: ANALYZE TABLE.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.