《高性能MySQL》语句片段

chapter05 创建高性能的索引

1、这样的语句是可以存在的

1 SELECT
2     sum(staff_id = 1),
3     sum(staff_id = 2)
4 FROM
5     payment t;

View Code

2、创建表userinfo的语句

 1 CREATE TABLE userinfo(
 2     id int unsigned not null auto_increment,
 3     name varchar(64) not null default '',
 4     email varchar(64) not null default '',
 5     password  varchar(64) not null default '',
 6     dob date default null,
 7     address  varchar(255) not null default '',
 8     city  varchar(64) not null default '',
 9     state_id tinyint unsigned not null default '0',
10     zip  varchar(8) not null default '',
11     country_id smallint unsigned not null default '0',
12     gender enum('M','F') not null default 'M',
13     account_type  varchar(32) not null default '',
14     verified tinyint not null default '0',
15     allow_mail tinyint unsigned not null default '0',
16     parrent_account int unsigned not null default '0',
17     closest_airport varchar(3) not null default '',
18     primary key(id),
19     unique key email(email),
20     key country_id(country_id),
21     key state_id(state_id),
22     key state_id_2(state_id,city,address)
23 )ENGINE=innodb;

View Code

3、通过STRAIGHT_JOIN干扰表的关联顺序

 1 -- 通过STRAIGHT_JOIN干扰表的关联顺序/
 2 EXPLAIN SELECT STRAIGHT_JOIN
 3     film.film_id,
 4     film.title,
 5     film.release_year,
 6     actor.actor_id,
 7     actor.first_name,
 8     actor.last_name
 9 FROM
10     sakila.film
11 INNER JOIN sakila.film_actor USING (film_id)
12 INNER JOIN sakila.actor USING (actor_id);

View Code

4、如何查看变量的值

1 show variables like  'optimizer_search_depth';

View Code