using normal primary key auto-increment or composite key in mysql

Question

for mysql and inoDB engine and tables that most use for read(not much create and edit compare with read queries)

tables are for blogging platform(user can create blog for themselves) and here are my two option for table schema:

for example for table posts:

1: posts(id, blog_id, title, content) => id is the only primary key auto increment

2: posts(id, blog_id, title, content) => composite primary key is (id, blog_id) and I use trigger before create query to increment id column

and in most of my queries there is two type of where:

1: for retrieve a single post:

where id=5 for solution one

where id=2 and blog_id=3 for solution two

2: for retrieve multi posts of single blog

where blog_id=3 for solution one

where blog_id=3 for solution two

question: which one is better for performance?

ps. if my question is not clear tell then I explain more.

tomvo tomvo · Accepted Answer · 2015-12-22T16:47:38

In short, performance-wise it does not matter. With most commonly used DBMS's and in most scenario's you won't see much difference between the two.

However, the most important thing to consider, is a simple one: compatibility and ease of use. Most ORM systems and frameworks are tuned to work with one column being the PK. So if you choose a composite key, it will always require a bit of tuning. Secondly, if you're going for a REST approach in your web application, working with singular ID's makes your life easier as well.

using normal primary key auto-increment or composite key in mysql

2 Answers