CODEWITHSUNDEEP
phpmysqlindexingforeign-keysinner-join
user2722667
user2722667

Reputation: 8661

Is index key enough or should I add foreign key

I am not sure if I should add foreign key to my table, as for now I only use index key on INNER JOINS.

Eg: I have 4 tables.

Ad > primary key ad_id

User > primary key user_id

State > primary key state_id

City > primary key city_id

On my ad table.

Each ad have a seller_id and a buyer_id, those rows inner joins to the user table on user tables row user_id which is a primary key in that table.

Each ad also have a state_id and city_id that inner joins to the city / state table where those rows are a primary key.

As for now I have only set those fields in my Ad table as index keys, those index keys then inner join to a primary key in another table. Should I also set those index keys in the ad table as foreign keys?

Thanks

Upvotes: 0

Views: 55

Answers (1)

duffymo
duffymo

Reputation: 308848

Foreign keys bring one-to-many relationships to mind. That's not what you have here.

You should add indexes to columns that participate in WHERE clauses above and beyond your JOINs on primary keys.

Upvotes: 1

Related Questions