Recently I have developed a website that had user generated data growing steadily. The database used is sqlite3. Before putting it up on the internet, I made sure to verify that all the pages are performing reasonably well (sub-second response times). Then, as time passed, one of the pages started slowing down. And with each passing day, it was getting bad. Then, after searching the web for some performance tips for SQLite, I noticed that the order of the tables matter! Yeah, coming from a Oracle CBO (Cost Based Optimizer) background, I wasn’t expecting that I had to do that, but apparently yes, the order matters. So, after changing the order, the reports are now back to sub-second response time. What’s better is, given that my reports have a time interval, no matter how much the underlying tables grow, the report is likely to take relatively constant time.
So, make sure that in the from clause, first place the table or sub-query that is very selective and then the rest of the tables. The rest of the tables are typically used to resolve a few foreign key references of the main table and usually not selective. For example, if userid is a foreign key and you need to get the username but the report is not selective by users, then it’s better to place the users table after the transaction table that gets filtered by the time interval or other parameters.