We've done some SQLite performance analysis and it seems that the size of a table has a significant impact on performance.
Since the wscache_2 table is one of the most used tables and also, the largest table by the number of records, it seems to be the very best candidate for optimisation.
We could implement a simple solution such as having four tables:
- wscache_core: for core_* WebService requests
- wscache_mod: for mod_* WS requests
- wscache_blocks: for blocks_* WS requests
- wscache_tool: for tool_* WS requests
- wscache_other: For the rest
Apart from the previous, we need a way to identify that there have been improvements, so maybe we should improve the current DB profiling tools for capturing DB queries and overall performance time during a session and presenting a report. Using the previous, we should be able to compare two executions (with and without the suggested improvements)