Status: Closed (View Workflow)
Affects Version/s: 30.2.3
limit=0 is used to get the totalRecords number without fetching records.
This is useless if estimated count is 8924 and actual count is 0, see
MODORDERS-407 "Cannot create POL due to POL limit bug".
Solution: For limit=0 use SELECT count(*) ... without LIMIT to get the exact count.
Background: We have changed the totalRecords algorithm in
RMB-591 "remove or harmonize 'optimizedSql' execution path" for performance reasons. However, RMB-645 'use where-only clause for the "count query"' removed the sortBy/order by clause from the count query reducing the execution time of the count query.
Other possible solutions:
- Always run SELECT count(*) ... LIMIT 1000 to get a precise hit count when the actual count is below 1000.
- Change factor 4 to 10, increasing 4000 to 10000.
- Run ANALYZE.
- Only change the hit count calculation when limit=0: If limit=0 run count-limit-1000.