Multi Disk/Multi Server Partitioning
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.