PERF: OPTIMIZE SQL QUERY PERFORMANCE

[somethingwithproof]

SUMMARY
The Syslog processing loop performs numerous database round-trips, particularly during the preprocessing phase where it searches for an available uniqueID for batching.

Existing pattern in syslog_preprocess_incoming_records:

1. Loop and execute SELECT COUNT(*) repeatedly with random IDs until a 0 count is returned.
2. Update the syslog_incoming table with the selected ID.

WHY REFACTOR
On high-volume systems, the repeated count queries add unnecessary latency to the polling cycle. Furthermore, many delete and optimization queries are executed unconditionally without verifying if data actually exists to be processed.

PROPOSED CHANGE

1. Efficient ID Generation: Replace the indefinite loop with a bounded retry loop and more efficient EXISTS or LIMIT 1 checks.
2. Conditional Optimization: Wrap maintenance tasks (OPTIMIZE, TRUNCATE) in checks to ensure they only run when appropriate.
3. Query Tuning: Review and optimize JOIN patterns in syslog_remove_items to ensure indexes are used effectively.

SCOPE

• Optimization of syslog_preprocess_incoming_records in functions.php.
• Refactoring of table maintenance logic.
• Performance verification via poller logs.