Skip to main content

Database Indexing in Django application

 


Database Indexing




Database indexing is a technique used to optimize the performance of database queries by allowing the database management system (DBMS) to quickly locate and retrieve specific rows of data. Indexes are data structures that provide a faster way to look up records based on the values stored in one or more columns of a table. When you create an index on a table, the DBMS creates a separate data structure that maps the values in the indexed columns to the corresponding rows in the table. Default Type of Index is B-Tree Index ( The king of all indexes)


বইতে কোন টপিক খুজতে গেলে আমরা টেবিল অফ কনটেন্ট থেকে দেখি এই টপিক কত নম্বর পেজে আছে।যাতে করে আমাদের পুরো বই খুজতে না হয়। ডেটাবেজ ইনডেক্সিং ও তেমনই একটা ইফিসিয়েন্ট টেকনিক।ডেটাবেজে কোন ডেটাকে দ্রুত খুজে বের করার জন্য ইনডেক্সিং করা লাগে।যদি এমন হয় একটা কুয়েরি বার বার এক্সিকিউট করতে হচ্ছে এবং একটা কলাম থেকে ভ্যালু বার বার খুজতে হচ্ছে তখন আমরা সেই কলামে ইনডেক্সিং করতে পারি।এর মাধ্যমে কোন ডেটা দ্রুত রিট্রাইভ করা যায়।কিন্তু ইন্টার্নালি কাজটা কিভাবে হয়?যখন আমরা ইনডেক্স ক্রিয়েট করি তখন ইন্টার্নালি সেই কলামের সবগুলা ডেটা আলাদা একটা ফাইলে রাখা হয়।এবং সেই ডেটা গুলা B-Tree ডেটা স্ট্রাকচার অনুযায়ী সাজানো হয়।B-Tree হলো বাইনারি সার্চ ট্রি এর মতই তবে কিছুটা এডভান্স।এই ট্রি এর লিফ সবসময় সেম লেভেলে থাকে এবং ট্রি সবসময় সেল্ফ ব্যালেন্স মেইনটেইন করে।বাইনারি সার্চ ট্রি তে একটা নোডে একটাই Key থাকে।কিন্তু এখানে একাধিক Key থাকে।এবং Key গুলা এসেন্ডিং অর্ডারে সাজানো থাকে।যার ফলে দ্রুত কোন ডেটা এখান থেকে রিট্রাইভ করা সম্ভব হয়।তবে অপ্রয়োজনী ইনডেক্সিং করলে ইফিসিয়েন্সি কমে আসে কারণ ইনডেক্সিং এর জন্য এক্সট্রা মেমোরি দরকার হয়।এছাড়া যেসব অপারেশনে রিড বেশি হয় সেখানে ইনডেক্সিং ভালো কাজ করে।কিন্তু আপডেট বা ইনসার্ট অপারেশন ইনডেক্সিং এর জন্য স্লো কাজ করে।
আমি ৫০ হাজার রো এর একটা ডেটাবেজে ইনডেক্সিং করে টেস্ট করলাম।রেসপন্স আসছে ৫০ মিলি সেকেন্ডে।আর ইনডেক্সিং ছাড়া অলমোস্ট ২০০ মিলি সেকেন্ড প্লাস।তার মানে আমরা টাইম লিমিট ৪ গুন কমাতে পারছি


B-Tree Index

# How does a B-Tree index work?

B-tree index is the most common in use. It achieves its goal by creating a tree structure of blocks containing key values in ascending order. Each of these blocks references another two child blocks, where left side keys keep value lesser than the current keys and the right side ones are more than the current keys. This way seeking values inside an index comes up to simple comparison calculations. B-tree can also handle equality and range queries on data that can be sorted into an ordering.

1. suppose you have these values: 1 2 3 4 5 6 7 8 9 

2. Now B-Tree index will create a tree as blow. Index Root and Leaf blocks



3. Now Search for the value 5 
    1. Now it'll Scan the index root

            
        

    2. Find the leaf block

    

    

    3. Search the value of the leaf block

    




Applying index into a django model. 




This is our model. Here we didn't apply any indexing in this model. If we want to get value using movie_key field filtering , It’s clear that PostgreSQL will decide to use sequential scan to retrieve the item, which simply means that it had to scan all table rows to finish its action. This practice has a clear impact on execution time value. Therefore, to improve the performance of the API, we should use an index on the movie_key field. Thanks to Django, we can make this change directly in our model by applying db_index=True in the desired field. After running the database migrations and sending the request once again, we can see the change in the executed query.

This time, the database engine used index scan on the index automatically created by PostgreSQL. This means that no sequence scanning was performed as the engine searched the index tree by providing a specific movie_key value. The execution time is significantly lower this time, coming down to less than 1ms.

Note: Database create default indexes in primary key

Surprisingly though, there is a way to optimize this query even more, allowing the database to skip the part of searching the table at all. This is achievable using a covering/inclusive index.

Covering / Inclusive Indexes 

They are useful when performing “value-for-value” lookups, which means retrieving one column value in the table based on another one. The index can be set up in the Meta class of our model. Additional field value is added in the include parameter of the UniqueConstraint index

for this query where Movie is the model : 

Movie.objects.filter(movie_key=movie_key).values_list('original_title',flat=True).get()

SELECT original_title FROM movie WHERE movie_key='Kjrkdj'




Cons:

1. Make index much bigger

2. Can sometimes replace composite indexes ( which are include lot of columns)


Labels

Labels:

Comments

Post a Comment

Popular posts from this blog

Implementing Advance Query Optimization in Django ORM

 Django's ORM makes database interactions seamless, allowing developers to write queries in Python without raw SQL. However, as applications scale, inefficient queries can slow down performance, leading to high latency and database load.  This guide explores advanced query optimization techniques in Django ORM to go beyond basic CRUD (Create, Read, Update, Delete) operations and improve efficiency.  1. Use QuerySet Caching to Avoid Repeated Queries Using cache reduces redundant queries for frequently accessed data. Caching helps reduce repeated database hits. 2. Avoid .count() on Large Datasets Using .count() on large tables can be expensive Inefficient way: Optimized way ( .exists() is Faster) 3. Use Indexes for Faster Lookups Indexes speed up queries on frequently filtered fields. Add db_index=True for frequently queried fields: 4. Optimize Bulk Inserts and Updated Performing operations on multiple records one by one is inefficient. Use bulk_create() for mass insert...
Post a Comment
Read more

Django select_related and prefetch_related

  Difference between select_related and prefetch_related Reducing SQL queries is one of the first steps when optimizing a Django project. There are two powerful methods included in the Django ORM to help us that can boost performance by creating a single more complex QuerySet rather than multiple, smaller queries. In this project we will understand about  select_related and prefetch_related.  Django use these two orm method to reduce sql queries in database based on different scenario.  select_related Lets assume  this two model we have.  class Author ( models . Model ): name = models . CharField ( max_length = 200 ) def __str__ ( self ): return self . name class Courses ( models . Model ): name = models . CharField ( max_length = 200 ) author = models . ForeignKey ( Author , on_delete = models . CASCADE , related_name = 'courses' ) def __str__ ( self ): return self . name Here we have two mode. ...
Post a Comment
Read more