I've got this query:
SELECT m.vlanID, v.vlan_name, m.interfaceName, count(m.macAddress) as mac, n.nd_name, p.interfaceDescription, p.interfacePortType FROM macs m, network_devices n, vlans v, ports p WHERE n.nd_ip_address = m.ipAddr AND m.vlanID = v.vlan_id AND p.vlanID = m.vlanID GROUP BY m.vlanID, v.vlan_name, m.interfaceName, n.nd_name, p.interfaceDescription, p.interfacePortType ORDER BY m.vlanID
Each of these tables have approximately 50.000 records. The query is taking north of 30min. Can I improve it to make it more efficient?
EDIT: Table definitions:
ipAddr, vlanID, macAddress, interfaceName
network_device_id, nd_location, nd_name, nd_ip_address, etc
interfaceName, interfacePortType, nodeIP, interfaceDescription
I've limited to mostly what's required from the query.
There's no indexes configured atm nor execution plan as this is not an application per se. This is just a basic script to be run occasionally for internal purposes only and the data will become obsolete within a few days, so we just need to query temporarily to get some answer while we upgrade our network.