0

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 ??

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
areviews app
  • 23
  • 6
  • 1
    Possible duplicate of [How does database indexing work?](https://stackoverflow.com/questions/1108/how-does-database-indexing-work) – sticky bit May 19 '18 at 03:59

1 Answers1

1

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thank you for your reply. Based on your experience for unique column which one better to use in mysql index or unique option ? – areviews app May 19 '18 at 15:06
  • @areviewsapp . . . They do different things. If you intend for the value to be unique, then use a unique constraint or index. If you don't want/need uniqueness, then you have to create a separate index. – Gordon Linoff May 19 '18 at 15:50
  • 1
    Sorry for many question, https://ibb.co/cdtLbT is there any different in speed between unique and non-unique index as the picture or both of them are fast ? Last question I noticed wordpress always use non-unique index even for emails that will not be duplicated that's why I'm asking because the emails always unique but they use index instead of unique do you have any idea ? – areviews app May 19 '18 at 16:03
  • 1
    @areviewsapp . . . I cannot speak for the Wordpress implementation. There is no performance difference, as far as I know, when using the index for looking up a single name. – Gordon Linoff May 19 '18 at 16:54