search

inverse-inc / packetfence

PacketFence is a fully supported, trusted, Free and Open Source network access control (NAC) solution. Boasting an impressive feature set including a captive-portal for registration and remediation, centralized wired and wireless management, powerful BYOD management options, 802.1X support, layer-2 isolation of problematic devices; PacketFence can be used to effectively secure networks small to very large heterogeneous networks.
https://packetfence.org
GNU General Public License v2.0
1.31k stars 276 forks source link

Optimize sql request for radius audit log #4100

Open fdurand opened 5 years ago

fdurand commented 5 years ago

When you go in radius audit log the following sql request is executed: SELECT COUNT(*) AS count FROM radius_audit_log WHERE ( radius_audit_log.tenant_id = '1' )

We need to optimize this request like: SELECT max(id) - min(id) AS count FROM radius_audit_log WHERE ( radius_audit_log.tenant_id = '1' ) and divide by the step of the id.

fdurand commented 5 years ago

ALTER TABLE radius_audit_log ADD INDEX tenant_id_id (tenant_id,id); SELECT ( ( max(id) - min(id) ) DIV ( select (select max(id) from radius_audit_log) - (select max(id) from radius_audit_log where id < (select max(id) from radius_audit_log ) ) ) ) AS count FROM radius_audit_log WHERE ( radius_audit_log.tenant_id = '1' );