I am trying to create indexes on several columns of a huge table in MySQL 4.0.12 running on Windows 2000 Server. The table contains 42 million rows, and occupies about 7G of disk space. The server is a 1.5GHz P4 with 1G RAM, and performs fairly well - the load data infile takes from 22 - 28 minutes with only a primary key index on an int with an empty table. All of the columns are NOT NULL, there is plenty of disk space, and I have tuned my.cnf to match the system. Here are the problems: 1. Even with a write lock, MySQL creates a copy of the data and index files before creating the new index. Is there any way to prevent this behavior? The copy routines used by MySQL in Windows appear to be inefficient, and the copy process takes appx 400%-600% longer than a simple file copy. Is MySQL performing some sort of integrity check during this process? And why the copy at all, if an exclusive write lock is obtained? Although this is not a huge issue, the additional 40+ minutes seems poorly spent (and gets worse the more indexes you have, as each existing index is somehow reprocessed). 2. I have a varchar(32) column that is 50% populated with strings 8-13 chars in length. When I try to create an index on this column, MySQL seems takes 15-20 TIMES longer than creating other indexes. I have created indexes on an int with sequential data (58 minutes), on the first 10 chars of a varchar(30) with random data (64 minutes), a varchar(2) + first 5 chars of a varchar(30) (84 minutes), and a datetime randomly spanning 90 days (72 minutes). All of these times also include the above mentioned file copy. During these indexes my CPU is averaging about 95%. When I create an index on the sparse (50% populated) varchar, the CPU won't even rise above 3%, EVEN DURING THE INITIAL FILE COPY, so it seems that MySQL canot effectively index sparse varchars on huge tables. Any suggestions? I am trying to deal a blow to the evil empire by proving MySQL as scalable as MS SQL Server, but the apparent poor implementation on win32 hurts. (If I do switch to Linux, whos version has the best performance and supports huge files?) Thanks in advance! Steve Allaway
