1

INFO I'm following lessons on databases. We're learning about indices now.

Question

If big parts of a table are in the memory, would using a index still have benefits?

My side

I would assume indexing makes a search always faster, so yes it would have benefits. I hope this is not to much opinion based and maybe someone have a good explained reason why or why not it has benefits.

RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
JochemQuery
  • 113
  • 4
  • 6
    OF COURSE! even if it's in memory, if you have to scan tens of thousands of pages for a full table scan, that's vastly less efficient than being able to walk through an index tree on a handful of pages..... also: it's much more likely that the tens of index pages are kept in memory, rather than ALL of thousands or tens of thousands of actual data pages – marc_s Nov 29 '14 at 10:10
  • thank you @marc_s Didn't thought of such big data tables. – JochemQuery Nov 29 '14 at 10:15
  • Regarding the SQL Server In-memory OLTP feature, the tables must have at least one index. – wBob Nov 29 '14 at 13:18

1 Answers1

1

Yes, an index will help. Without an index all the RDBMS can do is start at the beginning of the data and work its way toward the end, stopping when it finds what it's looking for. This is a O(N) operation. With a B-Tree index in place it reduces to O(log(N)). This is true whether the data is held on disk or in memory.

Michael Green
  • 24,839
  • 13
  • 51
  • 96
  • 1
    You wrote stopping when it finds what it's looking for but I guess it would traverse whole table even if it finds the required row this is how scan works – Shanky Nov 29 '14 at 11:41
  • 1
    If the query has an explicit or implicit limit, it may not need to scan the whole table. If a cursor is being used, it will stop scanning when it matches a row and resume scanning when the next row is requested. – BillThor Nov 29 '14 at 19:11
  • 1
    But what if it is not a cursor. statement was made as general statement which is wrong – Shanky Nov 29 '14 at 22:02