05 October 2018
A 100,000 activity entry database slows to a crawl when trying to exclude hidden activity entries on large datasets (e.g. count queries or hidden activity page). Exclusion and exclusion of hidden activity SQL needs significantly improved.
Removing hidden SQL results in a 25ms (will increase with more activity and hidden rows) being 10ms. Hidden activity page is inaccessible due to incredibly long query time.
It's due to using a single column to store either asset, item id, or user id.. split this into 3 more optimized columns then just do simple IN/NOT IN subqueries.
- % Done changed from 0 to 40
Part of the issue is the test data being used was for thousands of hidden rows that didn't ever exist. So this was throwing off performance checks. Cleaned up test data to represent real scenarios. JOINs appear to be ok with real test data.
- % Done changed from 40 to 60
- Status changed from Assigned to Resolved
- % Done changed from 60 to 100
- Status changed from Resolved to Closed
Also available in: Atom