0

If i have table with two column: id and priority.

  • Id is primary key
  • priority is number, unique and have indexing (Non-clustered).

So if i search by id or by priority. Which one is faster and why?

Le Dinh Dam
  • 112
  • 7
  • You could just try it (with both) and measure the time by yourself? That would have answered your question more quickly than it took aksing it here. . – cramopy Oct 21 '17 at 11:19
  • thanks for your quick response. but i want to know the algorithm behide. can u explain for me? – Le Dinh Dam Oct 21 '17 at 11:21
  • Also did you wanted to write `primary` key or not? If not, can you please explain what a `priority` key is? And `Which one is faster` instead of `quick one is faster` – cramopy Oct 21 '17 at 11:21
  • 1
    Both should be pretty fast as they are index seeks on unique indexes. In general the NCI might be faster as they are usually smaller and may have fewer levels (though not in your two column example) - but only if it contains all columns required by the rest of the query. Otherwise you will get a lookup back to the CI anyway. – Martin Smith Oct 21 '17 at 11:22
  • @LeDinhDam the algorithm is the same, except the fields you compare and which values you compare. Please just try it out and see which one is faster for your data in your database – cramopy Oct 21 '17 at 11:23
  • Your edit has changed the question. Are you saying that username isn't indexed after all? – Martin Smith Oct 21 '17 at 11:29
  • Sorry, i update my question again. because the type of id is a number and the type of username is probably some sort of varchar(). so maybe it affect the speed. – Le Dinh Dam Oct 21 '17 at 11:32
  • @cramopy: thanks for your reply, i updated the question. and also try to measure it. i see that id to be slightly faster than priority. – Le Dinh Dam Oct 21 '17 at 11:46
  • 1
    @LeDinhDam so you could have answered the question by yourself (kind of)? – cramopy Oct 21 '17 at 11:50
  • yep. @Gordon Linoff also answer me about the algorithm. – Le Dinh Dam Oct 21 '17 at 11:56
  • Which [DBMS](https://en.wikipedia.org/wiki/DBMS) product are you using? "_SQL_" is just a query language, not the name of a specific database product. –  Oct 21 '17 at 20:41
  • @a-horse-with-no-name, just ask about algorithm – Le Dinh Dam Oct 22 '17 at 01:23

1 Answers1

2

THIS ANSWERS THE ORIGINAL VERSION OF THE QUESTION.

This has little to do with clustered versus non-clustered indexing. Presumably, the type of id is a number (usually such columns are integers and hence four bytes). The type of username is probably some sort of varchar().

When searching through a B-Tree index -- whether clustered or not -- fixed length keys have a slight advantage. Hence, I would expect id to be slightly faster than username.

Does this make a difference in real world applications? I really doubt it. In a large-ish table, the time for query processing is typically dominated by I/O time loading the index and/or pages. Not always. But micro-optimizations (nano-opimizations?) generally have much less impact than one would expect.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786