What is the advantages of using auto_increment in database as primary key over set of attributes?

Question

I have table as

CREATE TABLE circle
(
    circle_id int(11) auto_increment primary key not null,
    user_id int (11) not null,
    title varchar(255) collate  utf8_general_ci not null
);


CREATE TABLE circle_share1
(
    id int(11) auto_increment primary key not null,
    circle_id int(11),
    user_id int(11),
    start_time date
);


CREATE TABLE circle_share2
(
    circle_id int(11),
    user_id int(11),
    start_time date,
    primary key(circle_id , user_id)
);

What are the advantages of using table share1 over share2 ?

share2 has primary key of circle_id , user_id 

and share1 has primary key id .

I checked 50 tables out of them none of the table is like share2 , Why developers are not preferred share2 ?

Answer 1

circle_share1 does not require much in-depth design thought. circle_share2 requires some actual brain cycles to determine if the primary key will have the desired performance.

Rows in circle_share1 will be written to the disk in the exact order they are inserted, thereby making inserts quicker.

Rows in circle_share2 may be inserted anywhere in the table, necessitating page-splits thereby fragmenting the data and possibly resulting in slower performance.

Neither way is the correct way in all circumstances - the best way depends on your data, and how it will be created in the user and circle tables and inserted into the circle_shareX table.