Worse performance after create empty table

Question

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.

Answer 1

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.)

Answer 2

In the post yesterday, invoice_requests table has 68k rows, and performance was very low.

Now, some new requests have come, and there are only 59 rows more.

I was repeating the query to capture the results and the EXPLAIN data, and, unexpectedly, the query takes less than a second.

I can't give any explanation, no change was made from the post yesterday. Maybe this is not an informative message, but I try not to leave opened post. Maybe I should wait more time to post my issue, but yesterday that query took 13-15 secondos dozens of times, in different moments.

So, thanks a lot.