CODEWITHSUNDEEP
sqldatabasepostgresqldatabase-design
Zanko
Zanko

Reputation: 4694

Database Design Performance of Many Table vs One Table

Say I have an ANIMAL table,

CREATE TYPE VALID_ANIMAL AS ENUM ('Dog', 'Cat', 'Pig');
CREATE TABLE IF NOT EXISTS ANIMAL (
  animal_type VALID_ANIMAL,
  name        TEXT,
  owner       TEXT,
  .... many more common fields
);

So if this table is very large 1 millions row with mixture of "Dog", "Cat" and "Pig" will it slow down the search for row containing "Dog"?

Or should I have 3 separate tables named DOG, CAT and PIG. This way the data is already separated and when querying for DOG I will just go to dog table. I am worried that one large table might have performance issue in term of filtering out "Cat" and "Pig" while searching for "Dog".

Upvotes: 0

Views: 167

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

If you have 1,000,000 rows then you probably have about 300,000 rows for each animal. You really cannot speed up a query that fetches one in three rows.

That is not strictly true. There are two things you can do. You can partition by the tables by animal type. A million row table is on the low end for partitioning.

The other thing is that you can create a clustered index on the animal type. In MySQL, you would do this by declaring a composite primary key with animal type as the first specified key.

Upvotes: 1

Related Questions