With same table structure there is different behavior of query plan on different environments-CodePudding

Case Inputs

Mysql Version on both Prod and test: 5.7

Query:

SELECT
    t1.id 
    t1.accountId,
    t2.col1
FROM
    table1 t1 
INNER JOIN table2 t2 ON
    (t2.Id = t1.id)
WHERE
    1 AND MOD(t1.id, 20) = 0 AND t1.col1 = 0 AND t1.col2 <= '2023-01-24' AND t1.col3 = 3;


                   Test        Production
Records in table1: 139664513   220184774
Records in table2: 139664513   220178452

Table structure:

CREATE TABLE `table1` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `col3` int(11) unsigned NOT NULL,
  `col2` date DEFAULT NULL,
  `col1` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `col3` (`col3`),
  KEY `col2` (`col2`),
  KEY `col1` (`col1`),
  KEY `idx_col3_col1_col2` (`col3`,`col1`,`col2`),
) ENGINE=InnoDB;

Explain Output:

Test explain OP

Prod explain OP

Because of not taking required index on test server it is taking a lot of time to execute compare to production.

Want to know why it is not taking required index on test server with same table structure.

I tried

Force index which works fine and the explain output is same as production, but why explain is not using required key without Force index, that's the concern.
To rebuild index

CodePudding user response：

ANALYZE TABLE table1; may fix the discrepancy.

KEY col3 (col3), is redundant with the composite key; you may as well DROP it.

Provide EXPLAIN FORMAT-JSON SELECT ... -- it may give more clues. If that does not suffice, try the "Optimizer trace" - http://mysql.rjweb.org/doc.php/index_cookbook_mysql#optimizer_trace

CodePudding user response：

Issue is resolved.

I dropped the index and recreate it with short length of index name and now it is picking up in explain plan.

Previously also I tried to recreate index but with same long name, which was not working.

I checked the identifier max length [https://dev.mysql.com/doc/refman/8.0/en/identifier-length.html]

The length of the index name in my case is 60 characters and in above link max length is 64. Still it resolved my issue.

Sorry but I can not upload the actual table details with name, because of data security.

Thank you for your quick response!