No problem!!
Please post the structures of your "big_table" and your "url_table"
(whatever you called them) and I will help you to rewrite step 4 to count
how many times a URL appears in the "big_table".
Is this bulk import a process you need to do repeatedly? if so we need to
worry about updating the count column on the next batch import and not just
re-creating it from the new data. That will change the query significantly.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"J S"
<[EMAIL PROTECTED] To: [EMAIL PROTECTED]
com> cc: [EMAIL PROTECTED]
Fax to:
06/13/2004 12:29 Subject: Re: load data into 2 tables
and set id
PM
>
>
>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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
