title: Creating index takes too long time
body: <p>About 2 months ago, I imported EnWikipedia data(http://dumps.wikimedia.org/enwiki/20120211/) into mysql.</p>
  
  <p>After finished importing EnWikipedia data, I have been creating index in the tables of the EnWikipedia database in mysql for about 2 month.</p>
  
  <p>Now, I have reached the point of creating index in "pagelinks".</p>
  
  <p>However, it seems to take an infinite time to pass that point.</p>
  
  <p>Therefore, I checked the time remaining to pass to ensure that my intuition was correct or not.</p>
  
  <p>As a result, the expected time remaining was 60 days(assuming that I create index in "pagelinks" again from the beginning.)</p>
  
  <pre><code>My EnWikipedia database has 7 tables: 
  "categorylinks"(records: 60 mil, size: 23.5 GiB), 
  "langlinks"(records: 15 mil, size: 1.5 GiB), 
  "page"(records: 26 mil, size 4.9 GiB), 
  "pagelinks"(records: 630 mil, size: 56.4 GiB), 
  "redirect"(records: 6 mil, size: 327.8 MiB), 
  "revision"(records: 26 mil, size: 4.6 GiB) and "text"(records: 26 mil, size: 60.8 GiB).
  </code></pre>
  
  <p>My server is...
  Linux version 2.6.32-5-amd64 (Debian 2.6.32-39),Memory 16GB, 2.39Ghz Intel 4 core</p>
  
  <p>Is that common phenomenon for creating index to take so long days ?
  Does anyone have a good solution to create index more quickly ?</p>
  
  <p>Thanks in advance !</p>
  
  <hr>
  
  <p>P.S: I made following operations for checking the time remaining.</p>
  
  <p>References(Sorry,following page is written in Japanese): <a href="http://d.hatena.ne.jp/sh2/20110615" rel="noreferrer">http://d.hatena.ne.jp/sh2/20110615</a> </p>
  
  <p><strong>1st. I got records in "pagelink".</strong></p>
  
  <pre><code> mysql&gt; select count(*) from pagelinks;
   +-----------+ 
   | count(*)  |
   +-----------+ 
   | 632047759 |
   +-----------+ 
   1 row in set (1 hour 25 min 26.18 sec)
  </code></pre>
  
  <p><strong>2nd. I got the amount of records increased per minute.</strong></p>
  
  <p>getHandler_write.sh</p>
  
  <pre><code> #!/bin/bash
  
   while true
   do
     cat &lt;&lt;_EOF_
   SHOW GLOBAL STATUS LIKE 'Handler_write';
   _EOF_
     sleep 60
   done | mysql -u root -p -N
  </code></pre>
  
  <p>command</p>
  
  <pre><code>$ sh getHandler_write.sh
   Enter password:
   Handler_write   1289808074
   Handler_write   1289814597
   Handler_write   1289822748
   Handler_write   1289829789
   Handler_write   1289836322
   Handler_write   1289844916
   Handler_write   1289852226
  </code></pre>
  
  <p><strong>3rd. I computed the speed of recording.</strong></p>
  
  <p>According to the result of 2. ,the speed of recording is</p>
  
  <pre><code> 7233 records/minutes
  </code></pre>
  
  <p><strong>4th. Then the time remaining is</strong></p>
  
  <pre><code> (632047759/7233)/60/24 = 60 days
  </code></pre>