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

Your Answer

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

Browse other questions tagged or ask your own question.