Doubts about INDEXING

Guys can someone explain me about the funtionality of INDEXING:

Python is zero-indexed. It means it starts counting the first item from zero. I will illustrate with this list: a_list =[1, 2, 3, 4, 5, 6, 7, 8, 9]. The index of this list is 0, 1, 2, 3, 4, 5, 6, 7, 8. So if you want to access the number one you can say a_list[0] and it returns 1. To see how python indexing works, run the following code:

for index, value in enumerate(a_list):
        print(index, value)

You will get something like this (0, 1), (1, 2)...etc

i am not really talking about indexing in python… i am talking about indexing sqlite tables… this is a chapter of dataquest course called Advanced SQL for Data Analysis

Okay. The initial question wasn’t clear. Can you please state exactly the problem with SQL indexing. I have completed the entire Data Analyst Path in Python and may be of help.

i’m trying to understand the real purpose of this kind of indexing because in course practices i can’t see the application of it

Okay.

I think indexing is about the speed of executing queries. It runs many times faster if you are dealing with large data.

Ok. Right, i understood it in lessons, parahaps ask you: what’s the meaning of this image:
image

is an exercises solution in the course that i referred to you

Hi @hlmeque,

As mentioned in @monorienaghogho’s response, indexes are used to speed up queries.

The most simple way that one can query a database table is to scan every single row, check if the data in that row matches the query conditions and output that row if it does.

This means that if there are, say 1,000,000 rows, every single row will need to be looked at. As the size of the table size increases the performance of the query decreases.

The SCAN TABLE fact is telling that the result of the query was calculated by scanning the whole fact table.

When we create an index on one or more columns, internally, the data on those columns is organized in a way that is much faster to search.

You can imagine a query as searching for some information in a book. In that sense, a sequential scan corresponds to reading the whole book to find it. It works, but it is very time consuming. Using an index would be analogous to using the book’s table of contents to pinpoint exactly the page where the information is located.

1 Like