Here's another option to load without requiring a primary key (requires a
LOT of extra disk space and fast CPU, and a batch window to run).
Load the new daily data into the table without checking for dupes.
Then create a new version of the table with distinct values.
Something like this (assuming your table has 2 columns which are char (3)
and char (5) for simplicity's sake) in pseudocode:
(remember to pad all columns to the maximum width to make them uniform,
right spaces on char, left zeroes on numerics)
CREATE NEW_TABLE AS
SELECT SUBSTR (DISTINCT (RPAD(COL1,3) || RPAD(COL2,5)),1,3) AS COL1,
SUBSTR (DISTINCT (RPAD(COL1,3) || RPAD(COL2,5)),4,5) AS COL2
FROM OLD_TABLE
matt ryan <[EMAIL PROTECTED]>
07/16/2004 07:43 AM
To:
cc: [EMAIL PROTECTED]
Subject: Re: Mysql growing pains, 4 days to create index on one table!
Donny Simonton wrote:
>Matt,
>I've been reading this thread for a while and at this point, I would say
>that you would need to provide the table structures and queries that you
are
>running.
>
>For example, we have one table that has 8 billion rows in it and it close
to
>100 gigs and we can hammer it all day long without any problems. It
really
>depends on how you are doing things.
>
>But as far as you mentioning about mysql not using multiple indexes, it
>does. You just have to create an index on multiple fields at one time.
>I've got tables with 10 fields in one index, now the trick with mysql is
>that you must use all top 10 fields in your where clause for mysql to
really
>take advantage of the index.
>
>But I would definitely send the list your table structure with your
indexes
>and some of your selects and inserts. You can always change the names of
>things if you don't want people to know the names of everything.
>
>Just my 2 cents.
>
>
Original email has the table structure, query speed is not an issue (it
is, but I will deal with that later)
the issue is insert speed, I get 150k-1M records daily, of these, only
5-10 % are new records, I load the EBCDIC file into a temp table, and
then do "insert ignore into historytable select * from temp table"
and I cant create multiple field indexes, I would need 100 indexes on a
table, the index side already exceeds the table size, I tried to add an
index on date, but gave up because it ran for 2 days and was not done yet.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
