mysql optimizing_读Effective Mysql:optimizing SQL Statements 笔记

http://effectivemysql.com/downloads/words 获取数据源

CREATE CHEMA IF NOT EXISTS book;

use book;

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);

CREATE TABLE million_words(

id INT UNSIGNED NOT NULL AUTO_INCREMENT;

word VARCHAR(50) NOT NULL;

PRIMARY KEY (id),

UNIQUE INDEX(word);

) ENGINE=InnoDB;

INSERT INTO million_words(word)

SELECT DISTINCT word FROM source_words;

INSERT INTO million_words(word)

SELECT DISTINCT REVERSE(word) FROM source_words

WHERE REVERSE(word) NOT IN (SELECT word FROM source_words);

SELECT @cint := COUNT(*) FROM million_words;

SELECT @diff :=1000000 - @cnt;

-- We need to run dynamic SQL to support a variable LIMIT

SET @sql = CONCAT("

INSERT INTO million_words(word)

SELECT DISTINCT CONCAT(word,'X1Y') FROM source_words LIMIT ",@diff);

PREPARE cmd FROM @sql;

EXECUTE cmd;

SELECT COUNT(*) FROM million_words;