adding data is slow

[Jon Drukman]

I've read everything I can about speeding up data insertion, but I still
can't get it to an acceptable speed.

My table is simple:

CREATE TABLE pageview (
  url char(128) DEFAULT '' NOT NULL,
  count mediumint(9) DEFAULT '0' NOT NULL,
  date date DEFAULT '0000-00-00' NOT NULL,
  site char(32) DEFAULT '' NOT NULL,
  KEY urlidx (url),
  KEY dateidx (date)
);

I'm using it store web traffic, of course.  For each day, I store every URL
requested and the number of requests.  Importing 30k rows when the table
already has somewhere around 500k rows is incredibly slow - 40 minutes!  I
load the data using the LOAD DATA INFILE command as recommended in the manual.

Dropping the indexes and recreating them after import is not a good
solution as it takes about an hour to rebuild each index.

Selects are actually quite quick - it's just importing.  Is there any way
to speed it up?

Jon Drukman
Director Of Technology
GameSpot