> 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. > 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/' | 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. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
