Mysql 8 – Count(*) – performance issue

MySQLmysql-8.0performanceperformance-tuning

I have this table with 2 million records

CREATE TABLE `my_table` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `ID_USER` int(11) DEFAULT NULL,
  `BODY` mediumtext CHARACTER SET utf8 COLLATE utf8_unicode_ci,
  `CREATION_DATE` date DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `ID_USER` (`ID_USER`),
  KEY `CREATION_DATE` (`CREATION_DATE`)
) ENGINE=InnoDB AUTO_INCREMENT=2198623 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

The first run of this select returns '1131' in 70 seconds

select count(*) from my_table where ID_USER=123 and CREATION_DATE>=2019-07-29

The second run returns '1131' in 60 milliseconds.

What do you recommend to improve performance?

More info:

  • ID as cardinality 2198623
  • ID_USER as cardinality 12788
  • CREATION_DATE as cardinality 11909

Best Answer

"Duct Tape" option: Remove the index on ID_User and replace it with an index on (ID_User,CREATION_DATE).

Best option: Get rid of the Id and find another suitable primary key such as (ID_User,<timestamp>). This is preferable as it would: ensure uniqueness, remove the need for one index, and get rid of a useless column.

Related Question