Data Indexing - Crucial to Speed

Discussion in 'Computer Speed' started by Dan Allen, Jan 15, 2016.

  1. Dan Allen

    Dan Allen Administrator Founder Not Banned Radio Button Problem - Leader

    This video shows a perfect example of how adding indexes to fields that SQL will use for matching records makes all the difference in the world to how fast the computer can complete a job.

    This video shows how to add indexes and the effect it has.

    In this case, indexing reduces the number of lookups the computer has to do from about 370 million to 10,000. 10,000 lookups can be done in a second or two. 370 million is going to take 37,000 times longer.

    Indexing can be complicated to understand, but it doesn't have to be. The basic idea is this. When data is indexed, the computer has a catalog that tells it where each record is. Without an index, the computer has to look at all the records it has to see which of them match what it is looking for. Usually, I avoid analogies but an analogy that has stuck with me is the idea that indexes are like card catalogs for a library and not having indexes is like trying to find books when they are randomly distributed throughout the library and there are no records or signs saying where any of them are. What if you had to find 10 books that way?
     
  2. Dan Allen

    Dan Allen Administrator Founder Not Banned Radio Button Problem - Leader

    This second video has the voice drowned out during some parts, so I am going to explain a little.

    0:00-1:00 - basics of sql for adding a record
    1:01-2:12 - use of variables instead of fixed values
    2:14 - 4:39 - assembling the sql for adding member levels to our 178 members missing Flash Fiction and General Affliate
    4:40- ~6:00 starting the test of the select part of the sql
    ~6:00 - 8:00 rebuilding the indexes again
    8:00 - 9:05, run the sql, looks good
    9:05 discover that the sql was wrong, adding the flash fiction level based on the criteria for missing General Affiliate.
    9:05-11:42 Rollback the table so we can start over with runnng the sql
    11:42-end run the sql and walk away
     

Share This Page