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.
VARCHAR(20)
is a problem itself. The problems with queries can't be solved without the queries.