On Sun, 2 Apr 2006, Chris Kantarjiev wrote: > To: mysql@lists.mysql.com > From: Chris Kantarjiev <[EMAIL PROTECTED]> > Subject: Re: stunningly slow query > > > The problem with Load Data is the larger the table, the > > slower it gets because it has to keep updating the index > > during the loading process. > > Um, thanks. I'm not sure how Load Data got involved here, because > that's not what's going on. > > > > > > It's a MyISAM table. Are there separate logs files? If so, where? > > > I thought it was just .frm, .MYI and .MYD. We have MYI and MYD on > > > separate drives. > > > > Log files usually default to the mysql data directory, eg. > > /var/lib/mysql/ > > As I said, I don't think there are any log files for a MyISAM table. > InnoDB has separate logs. > > > > > Putting the database files on seperate drives may slow > > things down alot too - unless others know better. > > > > .frm is the database definition file. .MYI is the index > > file, and .MYD is the data file. There is one each of these > > files for each myisam table in the database. > > > > I may be wrong, but I would have thought it better if these > > are all together on the same disk and partition for each > > table in the database? > > This is counter-intuitive. Separating .MYI and .MYD means that > I can overlap the i/o. This is a standard strategy for other > databases (Oracle, in particular). I would be really surprised > if this was causing my problem.
OK - something new I've just learnt Chris. > > This feature can be activated explicitly. ALTER TABLE ... > > DISABLE KEYS tells MySQL to stop updating non-unique indexes > > for a MyISAM table. ALTER TABLE ... ENABLE KEYS then should > > be used to re-create missing indexes. > > > 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', > `actual_TZ` varchar(30) default NULL, > `reported_Time` datetime default NULL, > `reported_TZ` varchar(30) default NULL, > `speed_Format` int(11) default NULL, > `speed` float default NULL, > `direction` char(2) default NULL, > `compass` int(11) default NULL, > `speed_NS` float default NULL, > `speed_EW` float default NULL, > `distance` decimal(10,0) default NULL, > `duration` decimal(10,0) default NULL, > `latitude` decimal(10,5) default NULL, > `longitude` decimal(10,5) default NULL, > `report_Landmark` varchar(255) default NULL, > `report_Address` varchar(255) default NULL, > `report_Cross` varchar(255) default NULL, > `report_City` varchar(255) default NULL, > `report_State` char(2) default NULL, > `report_Zip` varchar(10) default NULL, > `report_County` varchar(255) default NULL, > `category` int(11) default NULL, > `speed_Limit` int(11) default NULL, > `street` varchar(255) default NULL, > `city` varchar(255) default NULL, > `state` char(2) default NULL, > `zip` varchar(10) default NULL, > `county` varchar(255) default NULL, > `match_Name` tinyint(1) default NULL, > `name_Matched` tinyint(1) default NULL, > `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. > This is the other > > link_area | CREATE TABLE `link_area` ( > `link_ID` bigint(20) NOT NULL default '0', > `dir_Travel` char(1) NOT NULL default '', > `area_ID` int(11) NOT NULL default '0', > PRIMARY KEY (`link_ID`,`dir_Travel`), > KEY `link_area_ix_area_ID` (`area_ID`) > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Link area table' INDEX > DIRECTORY='/var/mysql_idx/landsonar/' | > > Inserts into the link_area were going very very slowly while data > was being moved into old_crumb. old_crumb is large - my suspicion > at this point is that the process of looking for key conflicts was > slowing things down and starving other query traffic. The same could be applied to the link_area table: Do you need dir_travel as part of the primary key? > link_area | CREATE TABLE `link_area` ( > `link_ID` bigint(20) NOT NULL default '0', > `dir_Travel` char(1) NOT NULL default '', > `area_ID` int(11) NOT NULL default '0', > PRIMARY KEY (`link_ID`), > KEY `link_area_ix_area_ID` (`area_ID`) > ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Link area table' INDEX DI$ Regards Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
