-1

Let's use lastName as an example.

Assuming that there are no duplicate last names in your DB (by chance, not because of a unique), would there be any benefit to indexing this lastName column?

The query that would be used to search would be something like SELECT * IN t WHERE lastName='Smith'.

If every entry in the column is unique, then how can an index have an effect? Wouldn't it have to search every entry regardless?

Sorry, I am just learning about indexing and I would really like to understand it better.

Thanks.

Nikita240
  • 1,377
  • 2
  • 14
  • 29
  • Imagine a table with 50million names. MySQL could retrieve all the 'Smith's with just a few reads to the index. Without an index it has no choice but to read all 50 million names. –  Jan 21 '15 at 01:25
  • @HoboSapiens I'm only using last names as a hypothetical example. Imagine there is only 1 smith in 50 million names. – Nikita240 Jan 21 '15 at 01:26
  • 1
    MySQL doesn't know how many it might find. Without an index it still has to search all 50 million. –  Jan 21 '15 at 01:27
  • Oh, so an index would prevent mysql from searching all 50 million if it encounters it early? – Nikita240 Jan 21 '15 at 01:28

2 Answers2

1

Yes, there is a benefit in indexing even if the column values are unique. In the index the values are not only unique but they are also organised in a tree structure that lets you search for a row with O(log N) complexity.

There is a great article in Wikipedia about it: Database Index

... The data is present in arbitrary order, but the logical ordering is specified by the index. The data rows may be spread throughout the table regardless of the value of the indexed column or expression. The non-clustered index tree contains the index keys in sorted order, with the leaf level of the index containing the pointer to the record (page and the row number in the data page in page-organized engines; row offset in file-organized engines).

In a non-clustered index

The physical order of the rows is not the same as the index order. The indexed columns are typically non-primary key columns used in JOIN, WHERE, and ORDER BY clauses. There can be more than one non-clustered index on a database table. ... Consider the following SQL statement: SELECT first_name FROM people WHERE last_name = 'Smith'; To process this statement without an index the database software must look at the last_name column on every row in the table (this is known as a full table scan). With an index the database simply follows the B-tree data structure until the Smith entry has been found; this is much less computationally expensive than a full table scan.

evpo
  • 2,436
  • 16
  • 23
  • Would there be an added benefit if the index is created on a prefix? – Nikita240 Jan 21 '15 at 01:27
  • 1
    Yes, by indexing, you effectively create another column that can be searched quickly and then proceed to get your final value. For example, it can find all entries starting with 'S', which could by Smith,Smythe, Styne. Then it only has 3 rows to find your entry of Smith. The index is fully searched but not the lastName column. – Kim Ryan Jan 21 '15 at 01:29
  • Thanks! It makes a lot more sense to me now. – Nikita240 Jan 21 '15 at 01:31
0

Generally speaking the more unique values there are in a column, or the higher its cardinality What is cardinality in MySQL?, the more useful an index will be on that column.

Community
  • 1
  • 1
David542
  • 104,438
  • 178
  • 489
  • 842