Re: select on varchar
Posted by:
Arnoud
Date: July 24, 2005 03:37AM
make test gave some problems so i switched to the binary of mysql 4.1.13.
Still the same problems.
Here is my create table for the first and second column:
CREATE TABLE `stats1` (
`id` int(11) NOT NULL auto_increment,
`browser_id` tinyint(3) unsigned default NULL,
`browser_version` varchar(16) default NULL,
`browser_engine` tinyint(3) unsigned default NULL,
`browser_plugins` smallint(5) unsigned default NULL,
`os_id` tinyint(3) unsigned default NULL,
`os_version` varchar(16) default NULL,
`screen_width` smallint(5) unsigned default NULL,
`screen_height` smallint(5) unsigned default NULL,
`screen_colordepth` tinyint(2) default '0',
`referer` varchar(255) default NULL,
`ip` varchar(15) default NULL,
`proxy` varchar(15) default NULL,
`tijdstip` datetime default NULL,
`titel` varchar(255) default NULL,
`hash` varchar(40) default NULL,
PRIMARY KEY (`id`),
KEY `idx_ip` (`ip`),
KEY `idx_browser_plugins` (`browser_plugins`),
KEY `idx_browser_id` (`browser_id`),
KEY `idx_browser_engine` (`browser_engine`),
KEY `idx_id_os_version` (`id`,`os_version`),
KEY `idx_browser_version` (`browser_version`),
KEY `os_version` (`os_version`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
And the merge table:
CREATE TABLE `stats_total` (
`id` int(11) NOT NULL default '0',
`browser_id` tinyint(3) unsigned default NULL,
`browser_version` varchar(16) default NULL,
`browser_engine` tinyint(3) unsigned default NULL,
`browser_plugins` smallint(5) unsigned default NULL,
`os_id` tinyint(3) unsigned default NULL,
`os_version` varchar(16) default NULL,
`screen_width` smallint(5) unsigned default NULL,
`screen_height` smallint(5) unsigned default NULL,
`screen_colordepth` tinyint(2) default '0',
`referer` varchar(255) default NULL,
`ip` varchar(15) default NULL,
`proxy` varchar(15) default NULL,
`tijdstip` datetime default NULL,
`titel` varchar(255) default NULL,
`hash` varchar(40) default NULL,
KEY `id` (`id`,`browser_id`,`browser_version`,`browser_engine`,`browser_plugins`,`os_version`,`ip`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 INSERT_METHOD=LAST UNION=(`stats1`,`stats2`)