List:General Discussion« Previous MessageNext Message »
From:Daniel Koch Date:June 24 1999 10:16pm
Subject:Re: adding data is slow
View as plain text  
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/


Thread
adding data is slowJon Drukman24 Jun
  • Re: adding data is slowSasha Pachev24 Jun
    • Re: adding data is slowJon Drukman25 Jun
      • Re: adding data is slowBenjamin Pflugmann25 Jun
        • Re: adding data is slowJon Drukman25 Jun
        • Yet Another Optimization QuestionJon Drukman28 Jun
  • Re: adding data is slowDaniel Koch25 Jun
  • adding data is slowMichael Widenius29 Jun
Re: Yet Another Optimization QuestionKevin Smith29 Jun
Re: Yet Another Optimization QuestionJon Drukman29 Jun
  • Re: Yet Another Optimization QuestionBenjamin Pflugmann30 Jun
    • Re: Yet Another Optimization QuestionJon Drukman30 Jun
Re: Yet Another Optimization QuestionKevin Smith29 Jun
Re: Yet Another Optimization QuestionJon Drukman29 Jun
  • Re: Yet Another Optimization QuestionT├Ánu Samuel29 Jun
    • Re: Yet Another Optimization QuestionJon Drukman29 Jun
      • Re: Yet Another Optimization QuestionMichael Widenius30 Jun
        • Re: Yet Another Optimization QuestionJon Drukman30 Jun
RE: Yet Another Optimization QuestionNigel Parker30 Jun
  • RE: Yet Another Optimization QuestionMichael Widenius30 Jun
Re: Yet Another Optimization Question(David Sklar)1 Jul
RE: Yet Another Optimization Questiontony2 Jul
  • RE: Yet Another Optimization QuestionMichael Widenius2 Jul
  • slow joinsJon Drukman7 Oct