MySQL Forums
Forum List  »  Partitioning

Multi Disk/Multi Server Partitioning
Posted by: chillyroll r
Date: April 16, 2009 05:02AM

Hi all,

I have a couple of questions:


I recently upgraded to MySQL 5.1.30-community just for the sake of using Partitioning feature. I have a table with more than 16.5 millions of records. And I intend to partition that using the key().

Below is my table structure:

CREATE TABLE `tbl_client` (
`client_id` int(20) NOT NULL DEFAULT '0',
`client_name` varchar(255) NOT NULL,
`client_type_id` int(20) NOT NULL,
`business_user_id` bigint(20) NOT NULL,
`city_id` int(20) NOT NULL,
`state_code` varchar(2) DEFAULT NULL,
`zip_code` varchar(10) DEFAULT NULL,
`latitude` double DEFAULT '0',
`longitude` double DEFAULT '0',
`created_on` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`modified_on` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
`is_deleted` enum('Y','N') CHARACTER SET utf8 DEFAULT 'N',
`source_id` varchar(255) DEFAULT NULL,
`source_key_id` varchar(50) CHARACTER SET utf8 NOT NULL,
`business_type_id_back` int(20) NOT NULL DEFAULT '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC COMMENT='partitioned using city_id' /*!50100 PARTITION BY KEY (city_id) PARTITIONS 10 */

Most of the queries I use for searching in the application refer to city_id (INT), state_code(VARCHAR) and client_type_id(INT) columns.

SELECT business_id,CONCAT(UPPER(SUBSTRING(city, 1, 1)), LOWER(SUBSTRING(city FROM 2))) AS cty from tbl_client WHERE city_id = '78646' AND state_code = 'CA' AND (client_name LIKE '%sports%' OR client_type_id IN ('33000000','21000000','59000000'))

This is just one of the query for which I switched to partitioning. Currently I have partitioned the table using key(city_code). Are there any better alternatives for partitioning this table?


Is it possible to store the partitions across different servers. (Did I say servers and not disks?). If yes, then how??? Any references???

Thanks in advance.

Edited 1 time(s). Last edit at 04/16/2009 05:05AM by chillyroll r.

Options: ReplyQuote

Written By
Multi Disk/Multi Server Partitioning
April 16, 2009 05:02AM

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.