Hello,
I had a question about indexing a while back and everyone screamed
"normalize"!!
Well...I've normalized much as I'm going to, and at most I have 3 indexes on
any one table. My database has 120 million records in it and the index
creation is taking a ridiculous amount of time. I can create the same
indexes on MS SQL or Oracle in a fraction (a small fraction) of the time.
Any tips? If I look at the PROCESSLIST, I can see that MySQL is using Key
Cache instead of File Sort. I've read that File Sort is faster but have no
idea how to force MySQL to use this method.
When MySQL indexes, does it actually create a copy of the table first (same
size as original .MYD) and then prune it back to a smaller size for the
.MYI? The reason I ask is because one table that I'm indexing has been
running for a long time and the .MYI is only 3GB and the .MYD is 12GB....not
a good sign.
Is PostgreSQL better for indexing?
I have a couple of tables with 1 or 2 indexes and they completed in 1 to
2hrs (same number of records). A couple of these inexes have larger fields,
for example VARCHAR(30) is the largest field in any index.
I also tried making a multi-part index "KEY idx_a1 (col1, col2, col3)" ...
that doesn't seem to be running much faster.
Let me know if anyone can help. There is nothing fancy about these tables,
like I said the largest field is an address field at 30 chars....most others
are ENUMs or 2-4 chars wide. My largest table has 30 columns (it indexed
very fast because it only had one index) and my smallest table has 3
columns.
Thanks.
Chris.
