MySQL Forums
Forum List  »  Archive Storage Engine

Date and Time type in archive table
Posted by: Zhu Dianbo
Date: December 23, 2009 01:29AM

Hi,
I have two tables whose DDLs are:
test_datetime:
CREATE TABLE `test_datetime` (
`if_id` int(10) unsigned NOT NULL,
`coll_type_id` tinyint(3) unsigned NOT NULL,
`value_diff` bigint(20) unsigned DEFAULT NULL,
`rate` int(10) unsigned DEFAULT NULL,
`coll_ts` datetime NOT NULL
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (if_id) PARTITIONS 1024 */;

test_time
CREATE TABLE `test_time` (
`if_id` int(10) unsigned NOT NULL,
`coll_type_id` tinyint(3) unsigned NOT NULL,
`value_diff` bigint(20) unsigned DEFAULT NULL,
`rate` int(10) unsigned DEFAULT NULL,
`coll_ts` time NOT NULL
) ENGINE=ARCHIVE DEFAULT CHARSET=latin1 /*!50100 PARTITION BY KEY (if_id) PARTITIONS 1024 */;

Then, i insert the same data (25,000,000 records) into these two tables except that the datetime data is convert to time data by time(coll_ts) before insert into the second table(test_time).

Last, i get the DATA_LENGTHs of these two tables from information_schema.tables, they are 118917198(test_datetime) and 121706132.

I am so surprised that i think '0000-00-00 00:00:00' is longer than '00:00:00', so is there anyone who can tell me why the table length with time column is greater than the table with datetime column.

Thanks and best regards,

Dianbo

Options: ReplyQuote


Subject
Views
Written By
Posted
Date and Time type in archive table
7811
December 23, 2009 01:29AM


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.