Is PRIMARY(ColumnA, ColumnB) + INDEX(ColumnB) + PARTITIONING(ColumnB) redundant? 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)?
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