mysql - What is the advantage of using composite primary key instead of single primary key in this context? -

cakephp v3 able support composite primary key. http://book.cakephp.org/3.0/en/quickstart.html

one example given @ quick start guide;

create table users (     id int auto_increment primary key,     email varchar(255) not null,     password varchar(255) not null,     created datetime,     modified datetime );  create table bookmarks (     id int auto_increment primary key,     user_id int not null,     title varchar(50),     description text,     url text,     created datetime,     modified datetime,     foreign key user_key (user_id) references users(id) );  create table tags (     id int auto_increment primary key,     title varchar(255),     created datetime,     modified datetime,     unique key (title) );  create table bookmarks_tags (     bookmark_id int not null,     tag_id int not null,     primary key (bookmark_id, tag_id),     index tag_idx (tag_id, bookmark_id),     foreign key tag_key(tag_id) references tags(id),     foreign key bookmark_key(bookmark_id) references bookmarks(id) ); 

the quick start guide mentions "you may have noticed bookmarks_tags table used composite primary key. cakephp supports composite primary keys everywhere, **making easier build multi-tenanted applications**."

what meant multi-tenanted applications? in context, why better use composite primary key versus single primary key?

in nutshell: clustering.

innodb automatically cluster (physically group together) bookmarks_tags rows same bookmark_id, making queries¹ extremely fast because dbms doesn't have "jump" on table gather related rows.

there no need surrogate key (auto-increment int) in case, nor additional price you'd otherwise pay underlying index. more thorough discussion on natural vs. surrogate keys, take here.

while i'm not familiar cakephp v3, guess mean can prefix keys of tables tenant id , pay little in performance terms due clustering.

---

¹ such as: "find tags of given bookmark". , since have index on {tag_id, bookmark_id}, opposite query: "find bookmarks of given tag" fast.