MySQL Forums
Forum List  »  Partitioning

Key Partition Performance Measurement
Posted by: Imran Pathan
Date: May 25, 2010 01:05AM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Key Partition Performance Measurement
4302
May 25, 2010 01:05AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.