CODEWITHSUNDEEP
mysqloptimizationinnodbmyisam
Alex Palombo
Alex Palombo

Reputation: 145

Table converted from myISAM to INNODB is slowing down a query

I have a table that was converted from myISAM to INNODB that is slowing down a query. This is a big table with lot of indexes. MyIsam (on mysql5.6) returns result instantly, INNODB (on mysql5.7) takes 2 to 3 seconds. fnota is float. serieid and epnumber are int. Any idea why this is taking more time when doing index_merge?

Explain on MYISAM TABLE:


explain SELECT count( fnota ) , avg( fnota ) FROM myrates
          WHERE serieid =4376 AND epnumber ='149'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: myrates
         type: ref
possible_keys: serieid,epnumber,serieid_2
          key: serieid_2
      key_len: 8
          ref: const,const
         rows: 8207
        Extra: Using index condition

Explain on INNODB Table:

explain SELECT count( fnota ) , avg( fnota ) FROM myrates
          WHERE serieid =4376 AND epnumber ='149'\G
*************************** 1. row ***************************
          id: 1
 select_type: SIMPLE
       table: myrates
  partitions: NULL
        type: index_merge
possible_keys: serieid,epnumber,serieid_2
         key: serieid,epnumber,serieid_2
     key_len: 4,4,8
         ref: NULL
        rows: 2
    filtered: 100.00
       Extra: Using intersect(serieid,epnumber,serieid_2); Using where

Indexes:


SHOW INDEX FROM myrates;
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| myrates |          0 | fbid      |            1 | userid      | A         |     1405506 |     NULL | NULL   |      | BTREE      |         |               |
| myrates |          0 | fbid      |            2 | serieid     | A         |     8617224 |     NULL | NULL   |      | BTREE      |         |               |
| myrates |          0 | fbid      |            3 | epnumber    | A         |   139638192 |     NULL | NULL   |      | BTREE      |         |               |
| myrates |          1 | serieid   |            1 | serieid     | A         |      257656 |     NULL | NULL   |      | BTREE      |         |               |
| myrates |          1 | epnumber  |            1 | epnumber    | A         |       93431 |     NULL | NULL   |      | BTREE      |         |               |
| myrates |          1 | serieid_2 |            1 | serieid     | A         |      186213 |     NULL | NULL   |      | BTREE      |         |               |
| myrates |          1 | serieid_2 |            2 | epnumber    | A         |     3309332 |     NULL | NULL   |      | BTREE      |         |               |
| myrates |          1 | userid    |            1 | userid      | A         |      866339 |     NULL | NULL   |      | BTREE      |         |               |
| myrates |          1 | userid    |            2 | serieid     | A         |     4656575 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

SHOW CREATE TABLE MYRATES; 
Table: myrates
Create Table: CREATE TABLE `myrates` (
  `userid` bigint(10) NOT NULL,
  `serieid` int(6) NOT NULL,
  `epnumber` float NOT NULL,
  `nota` int(6) NOT NULL DEFAULT '0',
  `fnota` float NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `fbid` (`userid`,`serieid`,`epnumber`),
  KEY `serieid` (`serieid`),
  KEY `epnumber` (`epnumber`),
  KEY `serieid_2` (`serieid`,`epnumber`),
  KEY `userid` (`userid`,`serieid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Upvotes: 0

Views: 41

Answers (1)

Rick James
Rick James

Reputation: 142278

DESCRIBE does not precisely spell out the indexes you have. Please provide SHOW CREATE TABLE. It sounds like it is getting confused over your long list of overlapping indexes.

When you have a composite index such as (serieid, epnumber) you don't also need (serieid). Drop the latter index to 'fix' the problem.

It seems that key "userid" is redundant also, given that "fbid" starts with both of its two columns in the same order.

Upvotes: 1

Related Questions