Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


MySQL Index Ongoing, More Than 12 Hours!

Posted on 2011-10-08
Medium Priority
Last Modified: 2012-05-12
I'm creating an index on a MySQL table with 25,000,000 rows.

The index is on 4 columns which are all VARCHAR(45), average being around 5 characters.

I started this 12 hours ago and it's still running, it's using 14/16 of the server load, around 10% of the RAM. The server is dual quad core 3Ghz, 12GB RAM.

I can't login to SSH to check on progress of anything because of the high load. Basically I'm locked out of the server while it's doing its processing and I have no idea how long it's going to last.

Is it supposed to take this long? Anything I can do to check on progress and/or speed it up?


EDIT: I managed to check running processes through WHM and it's currently on "Repair with keycache". Is that near the beginning or the end?
Question by:ADFB
  • 2
LVL 24

Accepted Solution

johanntagle earned 2000 total points
ID: 36935915
That means MySQL is using the (very much) slower method to index.  Can't explain it better, so just pasting from: http://stackoverflow.com/questions/3850582/determine-status-of-an-index :

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


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.

Author Comment

ID: 36936135
Thank you for the information...

The problem that I am faced with now is that I can't login to SSH because of the server load. It just hangs when I try to connect. How to solve that?
LVL 24

Expert Comment

ID: 36937124
I guess you'll need to ask support from your hosting company to do stuff via console.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This holiday season, we’re giving away the gift of knowledge—tech knowledge, that is. Keep reading to see what hacks, tips, and trends we have wrapped and waiting for you under the tree.
Microsoft Jet database engine errors can crop up out of nowhere to disrupt the working of the Exchange server. Decoding why a particular error occurs goes a long way in determining the right solution for it.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question