Jon Drukman wrote:
> 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
>
Shorten the key on the url column. Or try making another table with columns
url_id int and url, and put url_id in the pageview table.
--
Dan Koch
Webmaster
American City Business Journals
http://www.amcity.com/
