Mysql – Worse performance after create empty table

backupindex-tuningMySQLmysql-5.7performancequery-performance

I'm working with MySQL 5.7.10 and I have this issue.

I have this table to trace requests:

CREATE TABLE `invoice_requests` (
    `REQUEST_ID` VARCHAR(20) NOT NULL DEFAULT '' COLLATE 'utf8_spanish_ci',
    `INVOICE_ID` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8_spanish_ci',
    `STARTTIME` DATETIME NULL DEFAULT NULL,
    `ENDTIME` DATETIME NULL DEFAULT NULL,
    `STATUS` VARCHAR(10) NOT NULL DEFAULT 'WORKING',
    PRIMARY KEY (`REQUEST_ID`),
    UNIQUE INDEX `UNQ_INVOICE_ID` (`INVOICE_ID`),
    INDEX `IDX_REQ_CODE_END_TIME` (`REQUEST_ID`, `ENDTIME`),
    INDEX `IDX_INV_NUMBER` (`INVOICE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

This table had 350MB and last year data.
So I did the following steps:

  • RENAME TABLE invoice_requests TO 201805_invoice_requests;
  • run the 'create table' above.

The first executions with this new, empty table has worsened performance of some queries (from 1 second to 15).

With MySQL EXPLAIN, we have checked that no index is used in the INNER JOIN with other tables, but the queries are the same and
the JOINs are made with the indexed field INVOICE_ID.

For testing purposes, we quit the invoice_requests JOIN in the query and the requested data returned fast again.

With this scenario, my questions would be:

  • Are those the right steps to backup a table (and their indexes too)?
  • Do I need the "old" index data in the new, empty table? Supposedly, no. But I don't understand this behaviour.

Any help would be very appreciated.

Best Answer

It would help to see the slow query, and its EXPLAIN.

Meanwhile, try

ANALYZE TABLE invoice_requests;

There are no useful 'statistics' on an empty table. InnoDB should have recalculated the stats before you got to this problem, but we may be missing something.

Indexes are a mess:

PRIMARY KEY (`REQUEST_ID`),   -- notes 1,2
UNIQUE INDEX `UNQ_INVOICE_ID` (`INVOICE_ID`),  -- notes 1,3
INDEX `IDX_REQ_CODE_END_TIME` (`REQUEST_ID`, `ENDTIME`),  -- note 2
INDEX `IDX_INV_NUMBER` (`INVOICE_ID`)  -- note 3

Notes:

  1. Why have two unique indexes? Can't you live with just one? (Not knowing a "request" from an "invoice", I cannot advise further.)

  2. Don't bother adding an index that starts with the PK's column(s)

  3. A UNIQUE index is an index plus a uniqueness constraint. So, don't bother adding another index with the same column(s).

Having a bunch of tables like 201805_invoice_requests will be a problem in the long run. (There are many threads discussing having multiple identical table.)

Related Question