Indexing With Example

We had a task of reading the database values which is time based. So without indexing, the code takes a lot of time to fetch the values. One such situation was that when the reading was interrupted in between and the code now has to read a huge amount of data to fetch a single record which are present already along with the data coming in seamlessly. So in this blog post we are going to see how the problem was solved by using various order in indexing. Here cost and time plays a major role in choosing the index.

NOTE : All the queries ran in the Local Machine

QUERY:

SELECT “public”.”raw_events”.* FROM “public”.”raw_events” WHERE (timestamp >= 1449008122 and device_id = ‘logan134’ and account_id = ‘logan’ and processed = ’t’ and id != 11227294) LIMIT 1;

ANALYSYS:

1. Indexing on Account_id: 

indexing_1a

2. Indexing on Device_id: 

indexing_2a

3. Indexing on Timestamp (Default):

indexing_3a

4. Composite Index with Timestamp and Device_id: 

indexing_4a

5. Composite Index with Timestamp and Account_id: 

indexing_5a

6. Composite Index with Device_id and Account_id: 

indexing_6a

7. Composite Index with Timestamp, Device_id and Account_id:

indexing_7a

8. Indexing on Device_id with Composite Index on Device_id and Account_id: 

indexing_8a

9. Indexing on Device_id and Account_id (separate): 

indexing_9a

10. Composite Index with Device_id and Timestamp: 

indexing_10a

11. Composite Index with Device_id, Account_id and Timestamp: 

indexing_11a

CONCLUSION:

Based on the Analysis we found, creating composite index with Device_id, Account_id and Timestamp (Topic 11) is consuming less cost than the other indexes.

Surender T,
ROR Developer,
Mallow Technologies.

Leave a Reply

%d bloggers like this: