CODEWITHSUNDEEP
mysql
cicakman
cicakman

Reputation: 1990

Need MySQL key indexed?

Let say i have two tables,

for the sake of question, let's assume that they are two tables called customers and cars. 

Customers
id   name    age

Cars
id   customer_id  brand_id   engine    cc

Do we need to index customer_id? Does it give any advantage?

Upvotes: 1

Views: 49

Answers (3)

Ahsan Mahboob Shah
Ahsan Mahboob Shah

Reputation: 4029

like to highlight that on InnoDB, index automatically created on foreign key columns. see innodb-foreign-key-constraints

in your case customer_id if the foreign key constraint is applied.

Upvotes: 1

FabienM
FabienM

Reputation: 96

Depending on your application business logic and how you will query the base, having an index on customer_id will give you a huge advantage on queries like 

select * from customers join cars on customer_id = customers.id -- list all customers with their associated cars

Or even

select * from cars where customer_id = 2 -- list all cars for user 2

More generally, it is always a good idea to index foreign key constraints.

Upvotes: 1

davey
davey

Reputation: 1791

Yes it is, you probably want to join the customers table, you need to put a index on customer_id so the lookup can be done faster.

But like said in the comments, it depends, if you're not going to join the customers table (or do a WHERE / GROUP BY / ORDER BY etc. on it) and purely use it do display the id, it is not necassery.

Upvotes: 1

Related Questions