0

My question all is in the title. Why really an index makes a query so fast?! I've read some articles about indexes and how to use them. But still I don't know what happens in the background when I create an index on a column?

Will it sort the rows? Ok so? You know, the speed of one of my queries decreased from 4 sec to 0.04 sec when I created a composite index on two columns.

Anyway I'm so interested in understanding what happens when I create an index? How they help to the performance?

Martin AJ
  • 6,261
  • 8
  • 53
  • 111
  • 2
    Take a peek at https://stackoverflow.com/questions/1108/how-does-database-indexing-work – John Cappelletti Jul 29 '17 at 14:19
  • 2
    See [This post on Database Administrators site](https://dba.stackexchange.com/questions/73081/mysql-how-index-speed-up-query-execution) and Google is an index of all pages on the web, you should use it before asking a question here – RiggsFolly Jul 29 '17 at 14:19
  • 1
    Possible duplicate of [How does database indexing work?](https://stackoverflow.com/questions/1108/how-does-database-indexing-work) – Isac Jul 29 '17 at 14:25
  • Try to find the phone number of "John Smith" in the phone book that is not sorted by names. – Paul Spiegel Jul 29 '17 at 14:34
  • @PaulSpiegel Great .. I will accept your answer as accepted one if you add your comment as an answer. – Martin AJ Jul 29 '17 at 14:43
  • Read about BTrees in Wikipedia. – Rick James Aug 13 '17 at 17:10

1 Answers1

1

Lets take a table with 50 records and it doesnt have an index.

You may want to query 49th record and when you type 

select * from <myTable> where Id='49'

What happens in the background is , every page or every row is iterated and checks for the id whether the row is 49th or not, as soon as it lands on the 49th , it pushes that row on screen. but the point to be noted here is every iterated row is pulled into memory first and then the final result is pushed onto the screen

What if you have added an index on the ID column. what happens is a separate table is created where it stores the ID values and its respective row addresses. So now when you query based on ID ,it quickly finds the row address of that particular ID and pulls that into memory and depending on whatever you wanted , a "*" or some columns , it filters in memory and pushes on screen.

this might help you

https://www.youtube.com/watch?v=rtmeNwn4mEg&t=601s

Sai Bhasker Raju
  • 531
  • 1
  • 7
  • 20
  • This isn't a very good explanation; it confuses the idea of a record having the `id` of `49` with the very different idea of the (same) record being the `49th` one in some ordering. It is not an essential feature of database systems that records be stored in any particular order. – High Performance Mark Jul 29 '17 at 16:34
  • I understood your point @HighPerformanceMark , but in the beginning i mentioned a table with 50 rows and i just gave a simple example here with those rows.. may be next time we can give a better example than this one – Sai Bhasker Raju Jul 29 '17 at 17:44