Re: speed up the data retriving
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