Reputation: 4694
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
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