3

I have a big table (30 millions rows) defined like this:

CREATE TABLE `drawing` ( 
`id` int unsigned NOT NULL, 
`artist` int unsigned NOT NULL, 
`imageUrl` text NOT NULL,
`uploadDate` timestamp NOT NULL,
PRIMARY KEY (`id`,`uploadDate`), 
KEY `uploadDate` (`uploadDate`),
KEY `artist` (`artist`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED /*!50100
PARTITION BY RANGE ( UNIX_TIMESTAMP(uploadDate)) (
    PARTITION up2009 VALUES LESS THAN (1262304000) ENGINE = InnoDB,
    PARTITION up2010 VALUES LESS THAN (1293840000) ENGINE = InnoDB,
    PARTITION up2011 VALUES LESS THAN (1325376000) ENGINE = InnoDB,
    PARTITION up2012 VALUES LESS THAN (1356998400) ENGINE = InnoDB,
    PARTITION up2013 VALUES LESS THAN (1388534400) ENGINE = InnoDB,
    PARTITION up2014 VALUES LESS THAN (1420070400) ENGINE = InnoDB,
    PARTITION up2015 VALUES LESS THAN (1451606400) ENGINE = InnoDB,
    PARTITION up2016 VALUES LESS THAN (1483228800) ENGINE = InnoDB,
    PARTITION up2017 VALUES LESS THAN (1514764800) ENGINE = InnoDB,
    PARTITION up2018 VALUES LESS THAN (1546300800) ENGINE = InnoDB,
    PARTITION up2019 VALUES LESS THAN (1577836800) ENGINE = InnoDB,
    PARTITION up2020 VALUES LESS THAN (1609459200) ENGINE = InnoDB,
    PARTITION up2021 VALUES LESS THAN (1640995200) ENGINE = InnoDB,
    PARTITION upNew VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

As you see:

  • Table is partitioned based on the value of uploadDate, per year. Since there are lots of rows, I've found it useful when filtering by date (that is a typical operation in our DB). Speed of filtering increased significantly.
  • Since the partition is based on uploadDate, and we have also the identifier id, the primary key must be composed by both id + uploadDate

Sometimes we also perform filtering by artist, then sorting on uploadDate. No performances issues here.

Instead, we've found INSERT operations very slow.

Question: is it still useful to have an index on just (uploadDate)?

  • is it still useful to have an index on just (uploadDate)? If index exists, it can be used. If no index, fullscan by at list a partition primary key will be used. – Akina Mar 21 at 16:13
  • Well, I'm focusing on the current amount of data and indexes that we need, and it is probably the cause of slow INSERTs. The correct question is "do I have slowdowns in sortings if I remove it and rely only in (id,uploadDate) key?" – TheUnexpected Mar 21 at 16:58
  • 1
    Look EXPLAIN. Then edit the query by adding IGNORE INDEX and look again. – Akina Mar 21 at 17:02
  • Show us the queries; partitioning is often of no use. – Rick James Mar 29 at 22:20
0

PARTITIONing is not a performance panacea.

For

WHERE artist = 'constant'
  AND uploadDate BETWEEN ...

INDEX(artist, uploadDate) on a non-partitioned table is as good (or better) than using partitioning.

Do you have other queries? So far you have not presented a convincing case for partitioning.

Will you be purging 'old' data? Being able to use DROP PARTITION instead of DELETE is one of the few benefits of PARTITION BY RANGE.

More Discussion

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.