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]
