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:
2. Indexing on Device_id:
3. Indexing on Timestamp (Default):
4. Composite Index with Timestamp and Device_id:
5. Composite Index with Timestamp and Account_id:
6. Composite Index with Device_id and Account_id:
7. Composite Index with Timestamp, Device_id and Account_id:
8. Indexing on Device_id with Composite Index on Device_id and Account_id:
9. Indexing on Device_id and Account_id (separate):
10. Composite Index with Device_id and Timestamp:
11. Composite Index with Device_id, Account_id and Timestamp:
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.