CODEWITHSUNDEEP
mysqldatabase-designindexing
Oleg Abrazhaev
Oleg Abrazhaev

Reputation: 2839

Is there a benefit to index mysql column if I always have different value in every row?

Question is for rows like timestamp, where always different value stored in every row.

I'm already search through stackoverflow and read about indexes, but I don't understand profit if no one value equals to another. So, index cardinality will be equal to number of rows. What the profit?

Upvotes: 1

Views: 476

Answers (2)

Walter Mitty
Walter Mitty

Reputation: 18940

An index is a map between key values and retrieval pointers. The DBMS uses an index during a query if a strategy that uses the index appears to be optimal.
If the index never gets used, then it is useless.

Indexes can speed up lookups based on a single keyed value, or based on a range of key values (depending on the index type), or by allowing index only retrieval in cases where only the key is needed for the query. Speed ups can be as low as two for one or as high as a hundred for one, depending on the size of the table and various other factors.

If your timestamp field is never used in the WHERE clause or the ON clause of a query, the chances are you are better off with no index. The art of choosing indexes well goes a lot deeper than this, but this is a start.

Upvotes: 2

Mureinik
Mureinik

Reputation: 311188

This kind of column would actually be an excellent candidate for an index, preferably a unique one.

Tables are unsorted sets of data, so without any knowledge about the table, the database will have to go over the entire table sequentially to find the rows you're looking for (O(n) complexity, where n is the number of rows).

An index is, essentially a tree that stores values in a sorted way, which allows the database to intelligently find the rows you're looking for (O(log n)). In addition, making the index unique tell the database there can be only one row per timestamp value, so once a single row is retrieved the database can stop searching for more.

The performance benefit for such an index, assuming you search for rows according to timestamps, should be significant.

Upvotes: 3

Related Questions