CODEWITHSUNDEEP
phpmysql
blueSky
blueSky

Reputation: 247

Single vs multiple blog tables

I'm creating a classic php blog and have a dilemma about single or two mysql tables approach.

In the first case actual blogs would be placed inside actual table (100 rows max), and archived posts inside archive table (20.000 rows max).

Both tables have the same structure.

Querying on actual table is very often and on archive is not so often.

But sometimes there are join and union queries - covering both tables.

Logically, performances are much better on a smaller table but - is that in my case enough reason to create two tables instead single one?

There is also third solution - single table with two partitions actual - 100 rowsand archive - 20.000 rows.

What to do?

Upvotes: 0

Views: 45

Answers (2)

O. Jones
O. Jones

Reputation: 108841

You wrote:

Logically, performances are much better on a smaller table

With respect, your intuition about this is entirely incorrect for tables containing less than about ten million rows. A purpose of SQL is to allow rapid retrieval of a few items from among many. Thousands of years of programmer labor (not an exaggeration) have gone into making this kind of thing very fast. You won't be able to outsmart that collective effort.

Put your items in one table. If you need to distinguish between active and inactive items, make a column called active or some such thing, and retrieve them with WHERE active=1 or some such query term.

If you think you're having performance problems you can add indexes to your tables. Read this. https://use-the-index-luke.com/

Upvotes: 1

Abhishek Keshri
Abhishek Keshri

Reputation: 3244

While designing databases, don't only think about how you will store the data; but think about all the possible cases:

  • How are you going to retrieve and update information?
  • Will there be different views and different permissions for different people?

In your case, archive seems like a subset of actual table. So a single table would be preferred with a row for keeping track of archived files.

Upvotes: 0

Related Questions