為了探討索引需要測試資料,但又不宜使用之前的亂數產生方式.
可以使用Linux系統裡面的字典檔,但是字典檔是文字檔,
需要將它匯入MySQL,所以今天主要探討MySQL讀取文字檔
的方法與動態SQL,搭配使用產生100萬筆資料進TABLE.
為後續索引的討論建立基礎.
使用 root 登入, 並且加 --local_infile ,以啟動MySQL Client的
local file操作功能.

建立Table 以輸入字典檔

CREATE TABLE source_words(
word VARCHAR(50) NOT NULL,
INDEX (word)
)ENGINE=MyISAM;

LOAD DATA LOCAL INFILE '/usr/share/dict/words'
INTO TABLE source_words(word);

Query OK, 479829 rows affected (1.52 sec)
Records: 479829  Deleted: 0  Skipped: 0  Warnings: 0

輸入了47萬餘筆資料.

接著建立另一個Table, 用來再加工以後可以達到100萬筆資料.

CREATE TABLE million_words(
id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
word VARCHAR(50) NOT NULL,
UNIQUE INDEX (word)
)ENGINE=InnoDB;

從source_words INSERT 到 million_words

INSERT INTO million_words(word)
SELECT DISTINCT word
  FROM source_words;

Query OK, 462984 rows affected (12.55 sec)
Records: 462984  Duplicates: 0  Warnings: 0

換個姿勢,再來一次

INSERT INTO million_words(word)
SELECT DISTINCT REVERSE(word)
  FROM source_words
 WHERE REVERSE(word) NOT IN
       ( SELECT word
           FROM source_words
       );

Query OK, 457240 rows affected (40.50 sec)
Records: 457240  Duplicates: 0  Warnings: 0

計算一下差距.
SELECT @cnt := COUNT(1) FROM million_words;
SELECT @diff := 1000000 - @cnt;

產生動態SQL語句
SET @sql = CONCAT("INSERT INTO million_words(word)
                   SELECT DISTINCT CONCAT(word, 'X1Y')
                     FROM source_words
                    LIMIT ", @diff);

將動態SQL語句轉成SQL Command
PREPARE cmd FROM @sql;

執行SQL Command
EXECUTE cmd;
Query OK, 79776 rows affected (2.34 sec)
Records: 79776  Duplicates: 0  Warnings: 0

SELECT COUNT(1)
  FROM million_words;
+----------+
| COUNT(1) |
+----------+
|  1000000 |
+----------+
1 row in set (0.10 sec)

不多不少剛好100萬筆.