MySQL Forums
Forum List  »  Partitioning

Re: speed up the data retriving
Posted by: deva clementraj
Date: May 02, 2011 05:37AM

sir

the table i have is like this

show create table bwfdata.tbl_data ;

CREATE TABLE `tbl_data` (
`TagId` varchar(50) NOT NULL,
`TimeStamp` datetime NOT NULL,
`Value` decimal(18,2) NOT NULL,
`Quality` varchar(10) CHARACTER SET latin1 COLLATE latin1_bin DEFAULT NULL,
`SamplingMode` varchar(50) DEFAULT NULL,
`Direction` varchar(50) DEFAULT NULL,
`NumberOfSamples` int(11) DEFAULT NULL,
`IntervalMilliseconds` int(11) DEFAULT NULL,
`CalculationMode` varchar(50) DEFAULT NULL,
`FilterTag` varchar(50) DEFAULT NULL,
`FilterMode` varchar(50) DEFAULT NULL,
`FilterComparisonMode` varchar(50) DEFAULT NULL,
`FilterValue` varchar(50) DEFAULT NULL,
`TimeZone` varchar(50) DEFAULT NULL,
`DaylightSavingTime` bit(1) DEFAULT NULL,
`[RowCount]` int(11) DEFAULT NULL,
KEY `idx_tagid` (`TagId`),
KEY `idx_tagidtime` (`TagId`,`TimeStamp`),
KEY `idx_time` (`TimeStamp`),
KEY `idxtime` (`TimeStamp`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1



SHOW TABLE STATUS LIKE 'tbl_data'

name :'tbl_data'
Engine :'MyISAM'
version : 10,
Row_fprmat:'Dynamic'
rows : 30991011
avg_row_length: 47
data_length: 1486396736
max_data_length : 281474976710655
index_length : 1811104768
data free: 0
auto_increment: null
create_time : '2011-03-28 12:56:40'
update_time : '2011-05-02 14:48:15'
Check_time : '2011-03-29 13:37:35'
collation : 'latin1_swedish_ci'
checksum : null
create_options: null
comment : null

EXPLAIN SELECT * FROM tbl_data2 ;
id :1
select_type :SIMPLE
tbale :tbl_data2
type : ALL
rows :51523688

SHOW VARIABLES LIKE '%buffer%';

bulk_insert_buffer_size :8388608
innodb_buffer_pool_size :112197632
innodb_log_bufer_size :2097152
join_buufer_size :131072
key_buffer_size :786432000
myisam_sort_buffer_size :72351744
net_buffer_length :16384
preload_buffer_size :32768
read_buffer_size :1024000
read_md_buffer_size :262144
sort_buffer_size :262144
sql_buffer_reslit :off

what to do sir
i want to use innodb not MyISAM as engine because insert statement
block hole table if i use myisam

so i start to create table with partiton it show these result

mysq>show create table bwfdata.datapt ;

CREATE TABLE `datapt` (
`Id` int(10) NOT NULL AUTO_INCREMENT,
`Timestamp` datetime NOT NULL,
`Value` varchar(45) DEFAULT NULL,
`TagId` varchar(50) NOT NULL,
UNIQUE KEY `Id` (`Id`,`Timestamp`),
KEY `id_index` (`Timestamp`)
) ENGINE=InnoDB AUTO_INCREMENT=51293055 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE ( YEAR(Timestamp))
SUBPARTITION BY HASH ( MONTH(Timestamp))
SUBPARTITIONS 12
(PARTITION p_2003 VALUES LESS THAN (2004) ENGINE = InnoDB,
PARTITION p_2004 VALUES LESS THAN (2005) ENGINE = InnoDB,
PARTITION p_2005 VALUES LESS THAN (2006) ENGINE = InnoDB,
PARTITION p_2006 VALUES LESS THAN (2007) ENGINE = InnoDB,
PARTITION p_2007 VALUES LESS THAN (2008) ENGINE = InnoDB,
PARTITION p_2008 VALUES LESS THAN (2009) ENGINE = InnoDB,
PARTITION p_2009 VALUES LESS THAN (2010) ENGINE = InnoDB,
PARTITION p_2010 VALUES LESS THAN (2011) ENGINE = InnoDB,
PARTITION p_2011 VALUES LESS THAN (2012) ENGINE = InnoDB,
PARTITION p_max VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */

mysql>SHOW TABLE STATUS LIKE 'tbl_data2';

name :'tbl_data'
Engine :'Innodb'
version : 10,
Row_fprmat :'Compact'
rows : 51273373
avg_row_length : 54
data_length : 2803220480
max_data_length : 0
index_length :1739735040
data free :1041235968000
auto_increment :51293055
collation : utf8_general_ci
create_options: partitioned



mysql > EXPLAIN SELECT * FROM tbl_data2 ;

id :1
select_type :SIMPLE
tbale :tbl_data2
type : ALL
rows :51214850
SHOW VARIABLES LIKE '%buffer%




mysql > SHOW VARIABLES LIKE '%buffer%';

bulk_insert_buffer_size :8388608
innodb_buffer_pool_size :49283072
innodb_log_bufer_size :1048576
join_buufer_size :131072
key_buffer_size :26214400
myisam_sort_buffer_size :36700160
net_buffer_length :16384
preload_buffer_size :32768
read_buffer_size :65536
read_md_buffer_size :262144
sort_buffer_size :262144
sql_buffer_reslit :off
pls help me wether i'm doing it correct or wrong and pls correct me

and i also what to add the index ,partition,subpartition first and last value in the disk and also explain it
pls help soon


deva

Options: ReplyQuote


Subject
Views
Written By
Posted
3356
April 28, 2011 04:41AM
1780
April 29, 2011 08:37AM
Re: speed up the data retriving
2182
May 02, 2011 05:37AM
1841
May 02, 2011 11:46PM
1830
May 05, 2011 12:50AM


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.