CODEWITHSUNDEEP
mysql
Mikhail
Mikhail

Reputation: 9007

MySQL column index versus separate table

I'm working on a project where one of the tables can potentially have many million rows. At the moment, however, it's split into multiple tables with identical setup, based on the use of the row. Each table is near half a million rows.

This can be done by adding one more column with a certain status, and adding an index to this column.

My question is -- what is a rule of thumb? Should I always stick to the design of a single table with an indexed "use-case" column, or is it preference based, performance based, or doesn't matter-type of decision?

Upvotes: 3

Views: 212

Answers (1)

martincho
martincho

Reputation: 4717

The point of using a relational database is using its relational capabilities.

Traditionally, a ER model would be created according to the restrictions of the problem and then translated to a proper schema. In my experience, it's better to have a normalized database because it provides robustness, something like using design by contract programming in a piece of software, but makes it more difficult to introduce changes without locking the database.

If you need performance, then you'll have to do whatever it takes to get it, and you have a lot of options. Maybe a NoSQL database or a columnar store database like MonetDB.

Upvotes: 1

Related Questions