Determine status of an index in mysql

Question

I created a new index on a table with 35million records and its been running for nearly 1 day now. Previously when I created indexes it took 20 minutes, there columns were however floats. The new idnex is on a varchar(45)

I used the processlist command which shows the index creation is still in progress with the following output

65417 | Repair with keycache | CREATE INDEX insert_index on checkins(dateinserted)

I was wondering if anyone could give me advice on finding out if the query is actually dead and is just sitting in the process list. Maybe something has gone wrong at some stage and I'm not aware.

Thanks

Answer 1

Your index is building, but very slowly.

MySQL has two methods available for building indexes:

1. by sorting. This is the fastest method, but uses up a lot of memory.
2. by keycache. Slow, slow, slow - but uses up little memory.

The keycache method is a bit like insertion sort: values are inserted into the index one at a time. This is the same method used by the server when the INSERT statement is used to add rows to the table.

The sorting method sorts all the values using quicksort, and then builds the index from that. It is very fast, but requires a lot of memory and temporary disk space.

Some server variables can increase the space available to the sorting method, and so allow it to work with larger tables. See myisam_max_sort_file_size

http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_myisam_max_sort_file_size

On Linux, you can track the progress of the index repair by checking the size of the temporary files that are used to build the index. The following command will list all the files held open by the MySQL process:

sudo ls -l /proc/[mysql-pid]/fd  

Then check out the size of ones with hashes in their name - these are the temporary files.

Answer 2

Keep in mind the index size will be 35M*45 at least. If it's a utf8 column then it will be 35M*45*3. That's over 4 gigs! If you don't have tons of RAM to support that it's going to have to do a lot of disk access and really kill performance.

Can you normalize this column out into another table?

If not do the values tend to vary sufficiently in say the first 8 characters? You might be able to get away with just indexing the first 8 then.