Table Size

[living liquid | Christian Meisinger]

i don't get it...

why does the table below with `refurl` as varchar
use less space then the table with `refurl` as integer????


~90MB ->
CREATE TABLE IF NOT EXISTS `variationStatistic::4` (
   `id` bigint(20) NOT NULL AUTO_INCREMENT,
   `week_from` int(11) NOT NULL,
   `week_till` int(11) NOT NULL,
   `year` int(11) NOT NULL,
   `from` int(11) NOT NULL,
   `till` int(11) NOT NULL,
   `refurl` varchar(2500) NOT NULL,
   `tid` int(11) NOT NULL,
   `cid` int(11) NOT NULL,
   `aid` bigint(20) NOT NULL,
   `ccb` int(11) NOT NULL,
   `variation` varchar(50) NOT NULL,
   `country` char(2) NOT NULL,
   `siteid` int(11) NOT NULL,
   `search_engine` enum('google','yahoo','altavista','ask','msn','lycos','search.live')
NOT NULL,
   `search_value_id` int(11) NOT NULL,
   `views` int(11) NOT NULL,
   `sales` int(11) NOT NULL,
   `amount` bigint(20) NOT NULL,
   `amountPeriod` bigint(20) NOT NULL,
   PRIMARY KEY (`id`),
   KEY `refurl` (`refurl`(1000)),
   KEY `all` (`variation`,`country`,`cid`,`tid`,`ccb`,`aid`),
   KEY `vwfwty` (`variation`,`week_from`,`week_till`,`year`),
   KEY `vartill` (`variation`,`till`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1460161 ;


~120MB ->
CREATE TABLE IF NOT EXISTS `variationStatistic::4` (
   `id` bigint(20) NOT NULL AUTO_INCREMENT,
   `week_from` int(11) NOT NULL,
   `week_till` int(11) NOT NULL,
   `year` int(11) NOT NULL,
   `from` int(11) NOT NULL,
   `till` int(11) NOT NULL,
   `refurl` int(11) NOT NULL,
   `tid` int(11) NOT NULL,
   `cid` int(11) NOT NULL,
   `aid` bigint(20) NOT NULL,
   `ccb` int(11) NOT NULL,
   `variation` varchar(50) NOT NULL,
   `country` char(2) NOT NULL,
   `siteid` int(11) NOT NULL,
   `search_engine` enum('google','yahoo','altavista','ask','msn','lycos','search.live')
NOT NULL,
   `search_value_id` int(11) NOT NULL,
   `views` int(11) NOT NULL,
   `sales` int(11) NOT NULL,
   `amount` bigint(20) NOT NULL,
   `amountPeriod` bigint(20) NOT NULL,
   PRIMARY KEY (`id`),
   KEY `refurl` (`refurl`(1000)),
   KEY `all` (`variation`,`country`,`cid`,`tid`,`ccb`,`aid`),
   KEY `vwfwty` (`variation`,`week_from`,`week_till`,`year`),
   KEY `vartill` (`variation`,`till`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1460161 ;