Here is the original table definition which contains 107547934 (~107.5M) rows
CREATE TABLE `twitter_tweets` (
`id_str` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`id_user` int(10) unsigned DEFAULT NULL COMMENT 'Twitter id of the user',
`created_at` datetime DEFAULT NULL,
`in_reply_to_user_id` int(10) DEFAULT NULL COMMENT 'Twitter id of the user being replied to, if any',
`text` varchar(1000) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`retweet_count` int(10) DEFAULT NULL,
`geo` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`retweeted` tinyint(1) DEFAULT NULL COMMENT 'Indicates whether the tweet has been retweeted at all',
`place` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`in_reply_to_status_id_str` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`in_reply_to_screen_name` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`tweet_source` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`query` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'None',
`iso_language_code` varchar(5) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id_str`,`tweet_source`,`query`),
KEY `id_user` (`id_user`),
KEY `id_str` (`id_str`),
KEY `query` (`query`),
KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
The disk space usage stats are
I changed the primary key to just id_str
, dropped the key named id_str
, dropped the column named geo
. I also changed the the column tweet_source
from varchar(100) to tinyint. However, I did add two float columns coordinates_long
and coordinates_lat
. The column geo
which was dropped only contained null values. The two new columns added also only contain null values.
Here is the defn of the changed table which contains 106766691 (~106.7M) rows:-
CREATE TABLE `twitter_tweets_new` (
`id_str` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`id_user` int(10) unsigned DEFAULT NULL COMMENT 'Twitter id of the user',
`created_at` datetime DEFAULT NULL,
`in_reply_to_user_id` int(10) DEFAULT NULL COMMENT 'Twitter id of the user being replied to, if any',
`text` varchar(500) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`retweet_count` int(10) DEFAULT NULL,
`coordinates_long` float DEFAULT NULL,
`coordinates_lat` float DEFAULT NULL,
`retweeted` tinyint(1) DEFAULT NULL COMMENT 'Indicates whether the tweet has been retweeted at all',
`place` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`in_reply_to_status_id_str` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`in_reply_to_screen_name` varchar(256) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`tweet_source` tinyint(4) NOT NULL COMMENT '1=user_timeline, 2=search',
`query` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'None',
`iso_language_code` varchar(5) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
`ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id_str`),
KEY `id_user` (`id_user`),
KEY `query` (`query`),
KEY `created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
The disk space usage stats are
When you compare the size of the 2 tables, the former (named twitter_tweets
occupies less space than the latter twitter_tweets_new
).
Although there was a net addition of one column, considering that the null values don't take much space and also that I reduced the index size by a lot and also changed one of the columns to a smaller datatype and that the new table has lesser number of rows than the original one, the table size of the new table twitter_tweets_new
should have been much lesser than that of twitter_tweets
.
Why could this be happening?