It seemed to have copied the entire table
(all files) into another set of files. Now
it is doing read-intensive work.

Why? Snapshotting to ensure consistency?
Must be bypassable!

> needs to create a tmp table then slices and dices until complete, then writes > the index to disk? maybe?

It seemed that it created an EXACT copy of the data files that it
was supposed to index. It created them in the same
dirs that the originals were in. It then ran out of tmp space in /tmp
and the index failed. It then deleted all the copy files. Ya know, If I
really cared, I could read the source. I don't care that much.
Actually, I might, now that I think about it.

I've set the /tmp to be in the large partition, and I've pumped
up all the various memory related parameters, I've got 1GB in
this box.


> Is informix/sybase more price compat than oracle? informix is faster than
> oracle last time I

Nah, Informix of a certain class is faster, and it costs more.
I had the Informix people pitching me last month. And the lesser
Informix is REAL slow.

Sybase is seen as a corpse that hasn't figured it out yet. I
wouldn't get a dime for trying.

No commercial alternatives allowed. If I can't figure it
out via MySQL or PostGreSQL, it'll be Oracle.

But I really think that a dual CPU Linux box with MySQL will be plenty
fast enough, once I've set it up right. I'm loading the data for
the 4th time, after blowing it away trying varous alternatives.

Since it loads in 1/2 hour, it is worth testing variations.

Does anyone know of a Solaris like iostat for Linux. The
one in the mpstat package is nasty. I want to see MB/s
and Service time, but this one makes me think in blocks
or sectors. Of course, I COULD hack it.

OK, here's the reason. MySQL originally created indexes
via 'alter table add index name (field1,field2,...) syntax.
"create index ..." is a synonym. And ALL alter tables
do the following:
-----------------------------------------------------------

ALTER TABLE works by making a temporary copy of the original table. The alteration is performed on the copy, then
the original table is deleted and the new one is renamed. This is done in such a way that all updates are automatically
redirected to the new table without any failed updates. While ALTER TABLE is executing, the original table is readable
by other clients. Updates and writes to the table are stalled until the new table is ready.

Note that if you use any other option to ALTER TABLE than RENAME, MySQL will always create a temporary table, even
if the data wouldn't strictly need to be copied (like when you change the name of a column). We plan to fix this in the
future, but as one doesn't normally do ALTER TABLE that often this isn't that high on our TODO.
---------------------------------------------------------------------------

Yack shit. This piss poor design might be reason enough not use it.
On the other hand, if I can create all indexes on one pass, it might
be worth it.