CODEWITHSUNDEEP
mysqlindexing
josh
josh

Reputation: 145

MySQL index key

I've been doing a little research on the MySQL index key for possible database query performance. I have a specific question that hasn't really been answered.

Can you index every field in a table? Or should you just stick to indexing fields that will be in your WHERE clause?

Thanks, sorry if this question has been answered before.

Upvotes: 0

Views: 72

Answers (1)

Vicky Thakor
Vicky Thakor

Reputation: 3916

A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and storage space to maintain the index data structure. Indexes are used to quickly locate data without having to search every row in a database table every time a database table is accessed. Indexes can be created using one or more columns of a database table, providing the basis for both rapid random lookups and efficient access of ordered records.

https://en.wikipedia.org/wiki/Database_index

You should not create INDEX on every fields of table. Use only column which is used for search purpose (i.e WHERE, JOIN).

Note: Index helps to SEARCH faster on table on other hand it has to perform additional INSERT, DELETE and UPDATE so it increase the cost of query.

Consider scenario: You 3 or n queries with 3 or n different fields on same table in this case how to choose index?

It depends on how many times you are executing particular query on table. If you are executing 2 queries very rare then don't create index on that two columns. Create index only on column which is used in query that is executing multiple times.

Upvotes: 2

Related Questions