CODEWITHSUNDEEP
phpmysqldatabaseprimary-keycomposite-primary-key
mohsenJsh
mohsenJsh

Reputation: 2108

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

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.

Upvotes: 0

Views: 84

Answers (2)

blispr
blispr

Reputation: 912

If using a PK of "id, blog_id" : then the leading column in your index is "id".

If your query is "where blog_id = 3" - this will not use the index , and will be slow , because blog_id is not the leading column.

Since ID is a unique value within the table - just have it as the PK. Otherwise its misleading, someone may think ID is not unique.

If your queries will often use "blog_id" as the criteria, then have a secondary index on that column.

Auto-increment "id" column is better than having a database-trigger in my view. Its fewer moving parts, and lesser amount of code to write.

Upvotes: 1

tomvo
tomvo

Reputation: 1439

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.

Upvotes: 1

Related Questions