On Mon, 3 Apr 2006 [EMAIL PROTECTED] wrote: > To: [EMAIL PROTECTED] > From: [EMAIL PROTECTED] > Subject: Re: stunningly slow query > > [EMAIL PROTECTED] wrote on 04/02/2006 05:35:59 PM: > > <snip> > > > > Can you post your show create table tbl_name statement for > > > > these tables that involve slow queries? > > > > > > | old_crumb |CREATE TABLE `old_crumb` ( > > > `link_ID` bigint(20) default NULL, > > > `dir_Travel` char(1) default NULL, > > > `customer_ID` int(11) NOT NULL default '0', > > > `source_ID` int(11) NOT NULL default '0', > > > `vehicle_ID` int(11) NOT NULL default '0', > > > `actual_Time` datetime NOT NULL default '0000-00-00 00:00:00', > <snip> > > > `last_Modified` datetime default NULL, > > > PRIMARY KEY (`customer_ID`,`source_ID`,`vehicle_ID`,`actual_Time`), > > > KEY `old_crumb_ix_reported_Time` (`reported_Time`), > > > KEY `old_crumb_ix_link_ID` (`link_ID`,`dir_Travel`) > > > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 MAX_ROWS=1000000000 > > COMMENT='List of breadcrumbs already rolled up.' INDEX > > DIRECTORY='/var/mysql_idx/trimble/' | > > > > I'm no DB expert Chris but do you really need to create a > > primary key index over 4 columns? > > > > What about something simple and possibly faster like adding > > a seperate ID primary key column to the table like: > > > > | old_crumb |CREATE TABLE `old_crumb` ( > > `ID` int unsigned not null auto_increment > > `link_ID` bigint(20) default NULL, > > `dir_Travel` char(1) default NULL, > > `customer_ID` int(11) NOT NULL default '0', > > `source_ID` int(11) NOT NULL default '0', > > `vehicle_ID` int(11) NOT NULL default '0', > > snip > > PRIMARY KEY (`ID`), > > snip > > > > An unsigned int will take an extra 4 bytes of storage space > > per row, and will give you an index range of 0 - 4294967295. > > > > If that is not enough range, an unsigned bigint will take an > > extra 8 bytes of storage space, and will give you an index > > range of 0 - 18446744073709551615. > > > > Although this will increase the amount of storage space > > required in the .MYD file, it may also decrease the amount > > of space required in the .MYI index file, as you would not > > be needing to store multi-column indexes. > > > > Keith, > Your method won't guarantee that there are no rows where the combination > of the values in those four columns fails to repeat in any other row. To > do that would require an EXTRA four-column unique index of type UNIQUE. > Your proposal would actually make the situation worse as now there would > be two indexes to maintain to achieve the same effect as the previous > single PK.
Thankyou for your expert reply Shawn. Is it not possible to mark each of those those column values as UNIQUE without them becoming a part of the index as well? Or is this a contradiction in terms? Regards Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
