0

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.

  • 1
    350MB/50M records having PK of VARCHAR(20) is a problem itself. The problems with queries can't be solved without the queries. – Kondybas Jun 4 '18 at 19:16
  • @kondybas, thanks for your tip. I will consider it. – yaki_nuka Jun 5 '18 at 10:47
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.)

  • Thanks for your tips. I have reviewed the indexes and the queries, and dropping the redundant indexes don't affect the queries performance. So, thanks a lot. – yaki_nuka Jun 8 '18 at 10:49
  • @yaki_nuka - True. Dropping the redundant indexes does not impact SELECT performance, but will improve INSERT performance a little. – Rick James Jun 8 '18 at 14:32
  • yes, that`s right. And finally, about the bunch of tables *_invoice_requests, they will be backed up soon, and only one invoice_requests table will stay in the schema. Thanks again. – yaki_nuka Jun 10 '18 at 16:31
0

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.

  • Possibly the automatic ANALYZE finally kicked in. – Rick James Jun 5 '18 at 17:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.