Cacher is the code snippet organizer for pro developers

Quick Review

inner join 根據條件去搜尋的話，並不會出現 null 的狀況
lefr|right (outer) join 去搜尋的話，會根據左表或是右表的的標準去顯示，代表有可能出現 null

Index Manipulate

show index from table_name
drop index index_name on table_name
ALTER table table_name add index index_name (filed_1, field2);

Index Explaination

MySQL 覆盖索引
mysql百万级数据量根据索引优化查询速度
B-Tree介紹以及插入演示

DB Architecture

大型網站架構設計

Example join test

create table `user` (
    `id` bigint(18) unsigned not null auto_increment,
    `name` varchar(200) not null,
    `sex` varchar(200) not null,
    `age` int(10) not null,
    `group` varchar(100),
    primary key (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

create table `user_family` (
    `id` bigint(18) unsigned not null auto_increment,
    `user_id` bigint(18) unsigned not null,
    `dad_name` varchar(200),
    `mom_name` varchar(200),
    `group` varchar(100),
    primary key (`id`),
    foreign key (`user_id`) references user(`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


select * from user as u, user_family as f where u.id = f.id;
select * from user as u inner join user_family as f on u.group = f.group and u.id = f.user_id limit 15;
select * from user as u left outer join user_family as f on u.group = f.group and u.id = f.user_id limit 15;
select * from user as u right outer join user_family as f on u.group = f.group and u.id = f.user_id limit 15;
select * from user where id > 1000 and id < 1010 union select * from user where `id` in (select user_id from user_family where `group` is not null) limit 50;