I'm using MariaDB 10.1 and the default InnoDB storage storage, and I have a few tables with currently 10 to 100 million rows. These tables will keep growing a few million per month, and it's mostly caching.
They either have a single-column primary key (
BIGINT) or a composite primary key (two
BIGINT columns) and no AUTO_INCREMENT, and I always insert, select or update by primary key. I also do a lot of joins by primary key, or selects
WHERE PK IN (1, 2, 3, 4...).
Also, these tables receive lots of updates per hour, and I usually update them in batches of 5000 or 10000 at a time. We have more inserts and updates than selects for some of these tables.
I have 3 questions:
It seems to me that simple selects by PK returning 1 row (
SELECT x, y FROM table WHERE pk = 123) will have no actual difference in performance with partitioning. Is that right?
What about joins or selects as
WHERE PK IN(SELECT PK FROM ...)? Will it cause more scans to join a partitioned table than a single table?
Considering I usually do a lot of concurrent batches (multiple servers may send data at the same time) using:
INSERT INTO X VALUES (1, 'A'), (2, 'B'), ... ON DUPLICATE KEY UPDATE ...or
REPLACE INTO X VALUES(1, 'A'), (2, 'B'),...
Will partitioning help with concurrent inserts and updates, say by being able to affect multiple partitions at the same time?
Thanks in advance.