CODEWITHSUNDEEP
mysqlsqldatabase
areviews app
areviews app

Reputation: 23

add index on column that contains unique values?

I have column that contain unique values and full table scan will take for ever.

from my understanding

If I use unique index it will start searching form the beginning of the table and then stop when it find the value

while non-unique index will search in closest range of where statement and that what I need

if I have column that contain 

Maple Louie    
Raphael Gudino    
Nereida Tankersley    
Alina Colletti    
Jeane Lamm    
Shonta Forsman    
Ciara Cardamone

if I used non-unique index will it search within the range like 

where name = Jeane Lamm

will the database search for names start with j letter only ?

or it will do full scan ??

Upvotes: 0

Views: 1660

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269593

Although indexes can be fully scanned, that is not really how they work for many operations.

When you have an equality, such as where name = 'Jeane Lamm', then the index does a look up. This is much faster than a scan.

The most common structure for an index is like a binary tree, so the search for a particular value is very, very fast. That is one reason why indexes can greatly improve the performance of queries.

Upvotes: 1

Related Questions