MySQL 8.0 – Steps for Deleting Large Number of Rows and Changing Index

deleteindexMySQLmysql-8.0

We daily collect data with multiple records per second. The table my_xpa_data has currently close to 100 Million rows and is growing fast.

table size around 8 GB (used to be 4 GB a couple of days ago)
index size around 10 GB (used to be 4 GB as well)

I executed a few delete commands affecting only a couple of thousand rows. Then i got greedy and tried to delete around a million rows at once DELETE from myTable WHERE ID < 100276673. After 45mins i aborted the action.

The table looks like this

| ID  |    id_external    | date_collected      | prg | spotName  | ID_Machine| q_result |
|=====|===================|=====================|=====|===========|===========|==========|
| 127 | 20201229231513000 | 2020-12-29 23:15:13 | 110 | 008-04232 |    95     | 0.986879 |
| 128 | 20201229231515000 | 2020-12-29 23:15:15 | 109 | 008-04232 |    95     | 0.986879 |

and

CREATE TABLE `my_xpa_data` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
    `id_external` BIGINT UNSIGNED NOT NULL DEFAULT '0',
    `date_collected` DATETIME NOT NULL,
    `prg` INT UNSIGNED NOT NULL,
    `spotName` VARCHAR(50) NOT NULL,
    `ID_Machine` INT UNSIGNED NOT NULL,
    `q_result` DOUBLE NULL DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX `ID_Machine` (`ID_Machine`, `spotName`),
    INDEX `idx_qresult` (`q_result`),
    INDEX `idx_xpa_idmachine_prg` (`ID_Machine`, `prg`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=223323358

Based on Rick James Post MySQL Big DELETEs i am aware that a big delete does require some preparation.

To reduce the space needed and to improve the query speed i wonder what should be changed

get rid of indexes that are not needed (for example idx_qresult)
may be split up date_collected into three columns: YEAR+Month (YYYYMM), Day (DD), Time (HH:MM:SS) or leave it out altogether because id_external has likely the same information
the table id_external is a timestamp (YYYY MM DD HH MM ss SSS example 2020 12 29 23 15 13 000) as far as i can tell the last three digits SSS are always zero.
move the combination of spotName, ID_Machine and possibly prg into a different table and reference it with an id

Old records can be deleted but some aggregates (average, median) must be kept for some time.

Requirements

Keep every record from the last weeks (if feasible likely 12 weeks)
Calculate and keep aggregates (average, median, quartile) after 12 weeks (hourly, daily)

Question

I am at a loss how to proceed / to start

Switch of binary logs (anything else?)
create a new table or tables to receive the records from my_xpa_data
Regarding columns: Do you know if splitting date_created into three columns (YYYYMM, DD, HH:MM:ss) makes it faster to delete all records for a certain month (202006) without using partition?
Does it make sense to use helper tables for date_created that contain Year and Month (id, YYYYMM) days (id, DD) and time (id, hhmmssSSS) only store the ids as a reference?
SELECT Copy rows from my_xpa_data into the new table(s)
Drop the old table my_xpa_data
Create a view named my_xpa_data so that our source code does not need to change much unless using a view to insert records is a bad idea?

Best Answer

Overflow...

Not relevant to the question, but you have disaster coming in a few weeks: INT UNSIGNED will overflow at about 4 billion. DELETEing rows will not prevent this.

Plan A: Get rid of the AUTO_INCREMENT PK if you have some other column(s) that is unique and could be used as the PRIMARY KEY.

Plan B: Change it to BIGINT. Better now than when the table gets bigger. (That is 4 bytes bigger, but do it if you must.)

Note: You can run it this way without losing the old table. This lets you make a mistake without losing data. (Eventually DROP the old table.)

Deletes...

Deleting a million rows, especially while the table is active, will take a long time because of needing to save them in case of ROLLBACK or crash recovery. The simple solution is to delete only 1000 rows at a time. See http://mysql.rjweb.org/doc.php/deletebig

Since you want to delete "old" rows, DROP PARTITION is an excellent option. But it takes some setup. See that link (and another page that it points to). Since you want 12 weeks, I recommend 14 weekly partitions.

Aggregates...

Build and maintain a "summary table" by hour. See http://mysql.rjweb.org/doc.php/summarytables

That will make the 'report' queries much faster.

Size...

Here are some suggestions on shrinking the disk space for the 'Fact' table. That will indirectly help performance some.

spotName VARCHAR(50) NOT NULL looks bulky. Can it be normalized?

DOUBLE takes 8 bytes; do you need 16 digits of precision? Or would a 4-byte FLOAT with 7 digits of precision suffice?

Get rid of most of the indexes once you build the summary table(s).

With rare exceptions, do not split up a date into its component parts. It leads to extra hassles in the code, performance hits, and may even increase disk usage. (Note that there is overhead for each column.)

YYYY MM DD HH MM ss SSS looks like 17(?) bytes. TIMESTAMP(3) is only 7 bytes. If SSS is always 0, then use a 5-byte TIMESTAMP. If you don't want to switch to TIMESTAMP, then consider DECIMAL(17,0), which will take 8 bytes and still look like a 17-char string or 17-digit number.

INT takes 4 bytes. Check out the smaller options. (I suspect you don't need millions of machine ids.)

VIEW...

In MySQL, a VIEW is essentially syntactic sugar. No space is saved. No performance is saved. Etc.

Rebuild...

To delete rows and add partitioning:

Create new table with partitioning, changed indexes, different datatypes, normalization, etc
`INSERT INTO new-table SELECT revised-columns FROM existing-table WHERE id > ...

Note that the WHERE indirectly does the DELETE (by copying over only the rows that are to be kept). That helps things run faster shrinks the result table size and defragments.

More...

After you have dealt with most of my suggestions, start a new query and we can discuss the index(es) and summary table(s) further. Do include the main SELECTs.

Related Solutions

Mysql – Are two indexes needed

An index can seek by a subset of characters, as long as you're searching from the left. E.g., "Inter%" can seek, "%net" will not.

However, the first character is not necessarily the character under which the article would be sorted. "The Internet" should go under "I", not "T". You probably need two fields, DisplayTitle and SortTitle; a single-character index on the latter may be worthwhile, but most likely a full-length index will be just fine.

Indexes are typically B-trees, and a seek will jump to the right location about equally quickly whether you have 10 or 100 entries per page. Scans are another matter, but I'd start with the simplest solution and add an extra index only if performance proves inadequate in practice.

Mysql – Finding rows for a specified date range

Another way to get the result is this. It finds first all groups that the teacher has surely taught (or is going to) by checking that she has started within the month and then in another subquery it finds - for every group - the last teacher that started at the first day of the month or earlier.

With the unique index you have on the table, the second subquery should be quite efficient. The first subquery would benefit from an index on (teacherid, startdate, groupid):

SELECT groupid
FROM pupilgroupteacher
WHERE teacherid = @teacher 
  AND startdate >= @month + INTERVAL 1 DAY
  AND startdate < @month + INTERVAL 1 MONTH

UNION DISTINCT

SELECT gg.groupid
FROM 
    ( SELECT DISTINCT groupid
      FROM pupilgroupteacher
    ) AS gd
  JOIN pupilgroupteacher AS gg
    ON  gg.groupid = gd.groupid
    AND gg.startdate =
        ( SELECT MAX(gi.startdate)
          FROM pupilgroupteacher AS gi 
          WHERE gi.groupid = gd.groupid
            AND gi.startdate < @month + INTERVAL 1 DAY
        )
WHERE gg.teacherid = @teacher ;