MySQL Paritioning performance

Question

Measured performance on key partitioned tables and normal tables separately. But we couldn't find any performance improvement with partitioning. Queries are pruned.

Using MySQL 5.1.47 on RHEL 4.

Table details:

UserUsage - Will have entries for user mobile number and data usage for each date. Mobile number and Date as PRI KEY.

UserProfile - Queries prev table and stores summary for each mobile number. Mobile number PRI KEY.

CREATE TABLE `UserUsage` (
`Msisdn` decimal(20,0) NOT NULL,
`Date` date NOT NULL,
.
.
PRIMARY KEY USING BTREE (`Msisdn`,`Date`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY KEY(Msisdn)
PARTITIONS 50;


CREATE TABLE `UserProfile` (
`Msisdn` decimal(20,0) NOT NULL,
.
.
PRIMARY KEY (`Msisdn`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
PARTITION BY KEY(Msisdn)
PARTITIONS 50;

Second table is updated by query select and order by date in first table in a perl program, query is

select * from UserUsage where Msisdn=number order by Date desc limit 7

[Process data in perl]

update UserProfile values(....) where Msisdn=number

explain partition for select, shows row being scanned in a particular partition only.

Is something wrong with partition design or queries as partitioning is taking almost same or more time compared to normal tables?

Answer 1

Is something wrong with partition design or queries as partitioning is taking almost same or more time compared to normal tables?

No. You partition a table so that you don't have to backup or reorganize the entire table at a time. Partioning allows you to better manage large tables. Partioning is not a performance enhancer.