MySQL – Why Not Use Partitioning?

database-designMySQLpartitioning

When would one NOT want to partition a database? (thinking MySQL partitioning)

In my case

I'll be starting with a couple millions rows, it should grow from there.
Primary key on a character field that serves as the most frequent query restraint (and lookups are frequent – at least a few per second).
Primary key would be hashed to serve as the partition key
Updates will be made to every row that is pulled in the frequent queries mentioned above
Less frequent lookups (against date columns or other) will need to hit all partitions

Even for the last point, doesn't the lookup run in parallel so in all cases, is this a win? What are the downsides to partitioning? Why isn't it something that EVERYONE uses by default, at least when you are looking at a million+ records?

UPDATE – I selected zgguy's answer but note that I added my own answer with the results of my own research including a link to a really good answer on a similar question that was highly useful to me.

Best Answer

There is no silver bullet for performance problems, and partitioning is not one either.

Every partition is essentially a table for itself. Hence queries that are written in a way that allows the database to look for rows in only one partition become faster. Difference can be huge for queries that would need to scan the entire large table, but can restrict themselves to scanning only one partition in the partitioned table. For unique key lookups, difference is much smaller.

However, queries that use index lookups in a way that requires the database to visit all or most of table(index) partitions will run considerably slower.

Parallel execution is a topic for itself. If you run large overnight batches, and have the entire machine to do that single job, then its parallelization is a good thing. However in an OLTP system where the database constantly serves queries from many concurrent users, you don't want one user to take up all the resources.

Related Solutions

SQL Server – Moving Rows Between Partitions by Updating Partition Key

I do not have a 2005 server to test with. 2008 however, appears to handle this as expected:

USE [Test]
GO
CREATE TABLE [IDRanges](
    [ID] [int] NOT NULL
)
GO

CREATE PARTITION FUNCTION IDRange1 (int)
AS RANGE LEFT FOR VALUES (10) ;
GO
--Add one record to each partition
INSERT INTO IDRanges ([ID]) VALUES (17)
INSERT INTO IDRanges ([ID]) VALUES (7)
GO
--Verify records in partition
SELECT $PARTITION.IDRange1([ID]) AS Partition, COUNT(*) AS [COUNT] 
FROM IDRanges
GROUP BY $PARTITION.IDRange1([ID]) 
ORDER BY Partition ;
GO
--Move row between partitions
UPDATE IDRanges
SET [ID] = 8 WHERE [ID] = 17
GO
--Verify records in partition
SELECT $PARTITION.IDRange1([ID]) AS Partition, COUNT(*) AS [COUNT] 
FROM IDRanges
GROUP BY $PARTITION.IDRange1([ID]) 
ORDER BY Partition ;

You should see one record in each partition before the update, and both records in the first partition afterwards.

Sql-server – Does the partition key also have to be part of the primary key

Not at all.

One of the most common scenarios for partitioning is to use a date field, which is totally unrelated to your PK.

For instance, if you have a table Orders with the field OrderDate you would most likely partition based on the month and year of OrderDate.

When records age out and are no longer relevant you can move those partitions off to an archive table or database so they are no longer processed.

Partitioning will work with pretty much any field, but in order for it to work WELL the field(s) you partition on should be used in most, if not all, of your queries. If you don't include your partition keys then you will get essentially an expensive table scan that goes across multiple tables (partitions).

EDIT

For part 2, I think the answer is no as well. The partition key is used to determine which partition to put the row in, but I don't think an index is maintained. There may be stats in the back end on it though.

Best Answer

Related Solutions

SQL Server – Moving Rows Between Partitions by Updating Partition Key

Sql-server – Does the partition key also have to be part of the primary key

Related Question