>
>
>Mos forgot to populate the url_id column in your user table. I would use
>his same process but re-arrange it like this:
>
>1) create table BIG_TABLE....
>2) load data infile....
>3) create table URL_TABLE (
> url_id bigint not null auto_increment,
> url varchar(25) not null primary key,
> category ....
> )
>4) INSERT IGNORE URL_TABLE (url, category)
> SELECT url,category
> FROM BIG_TABLE
>4) create table USER_TABLE (
> user_id varchar?(...) primary key,
> date_time datetime,
> url_id bigint,
> size int
> )
>
>5) insert USER_TABLE
> SELECT bt.userID, bt.datetime, u.url_id, bt.size
> FROM BIG_TABLE bt
> INNER JOIN URL_TABLE u
> ON u.url = bt.url
>
>doing it this way lets the SQL engine handle the conversion of URLs to
>their IDs in the USER_TABLE...see? No scripting required at all!
>
>Yours,
>Shawn Green
>Database Administrator
>Unimin Corporation - Spruce Pine
>
>
Shawn,
Many thanks for your reply. It sounds like this might be quicker than my
perl script which parses a 1GB log file and fills the database in 40 mins at
the mo. (The actual parsing only takes 1.5 mins).
There's one snag which I forgot about and that's in the url_table I have
another column called hits which is the number of hits for each url. I'm
terrible at SQL and wondered if you might be able to suggest a way of doing
this with the method above?
Thanks,
JS.
_________________________________________________________________
It's fast, it's easy and it's free. Get MSN Messenger today!
http://www.msn.co.uk/messenger
