Database Index Packs is a collection of composite indexes for the Connectwise Automate database. It contains a total of 42 composite indexes spread across 30 tables, and it’s designed to be an entry-level optimization measure for the Connectwise Automate MySQL database for on-premise and cloud-hosted servers.

The Automate database ships with ~1100 indexes by default. However, not all indexes are created equal. 98% of the indexes that ship with Connectwise Automate are single-column, and this results in non-optimal retrieval of data. MySQL can only use one index per query in most cases, so any queries filtering by multiple disparate indexes (like locationid and clientid in the computers table) would be forced to pick one index, or to forego the use of the indexes altogether. This explains why 27% of the stock indexes are not used by the database at all.

Index performance comparison

Here at Automation Theory, we like to have measurable results. Below are a couple of example queries that run each time a computer is opened in the desktop client.

First, we have what looks to be a less-optimized example. Each time an agent is opened all the subnet mask data is populated from the inv_networkcard table. With the stock indexes, this produces a full table scan.

SELECT `NetMask` AS `NetMask.7765e09c-efaa-453e-8c20-6e937f88bed3` 
FROM `computers`   LEFT JOIN `inv_networkcard` ON `computers`.`MAC` = `inv_networkcard`.`MAC` 
WHERE `computers`.`ComputerID` = 2 AND `computers`.`MAC` <> '';

After we install the Database Index Packs we see that this has improved to a full index scan (which is still rather inefficient in the grand scheme of things). However, switching from the graphical explain to the tabular output we see a hidden gem: the “using index” message. This indicates that all the data for the query is contained in the index, and thus the query can run without ever having to read data off the disk (since indexes are normally cached in RAM). This results in the query running much faster.

SELECT p.ComputerID, p.Name, SUBSTRING_INDEX(SUBSTRING_INDEX(procpath, '\\', -1),".",1) AS ProcPath 
FROM processes p  
WHERE p.ComputerID = 1 GROUP BY p.Name;

This next query runs when loading process data for a computer in Automate. This particular query is grouping the processes by name from a particular computer. Without an index, MySQL is forced to use a method known as a file sort to process this request whereby it grabs all the matching rows, creates a temporary table (potentially written to disk for large datasets), and then uses the temporary table to perform the group operation.

After loading the Database Index Packs we can see that we’re in the same order of magnitude with pulling the data from the table. However, we can see that since the index contains the column the query is grouping by it can return the matching rows without needing to file sort and create a temporary table. We can also see that the number of rows returned from the table itself has dropped (a 36% improvement).

Select `workspacedatatilerelations`.`WorkspaceGUID` AS `Guid`,`workspacedatatilerelations`.`DataTileGUID` AS `DataTileGuid`,`workspacedatatilerelations`.`Order` AS `Order`,`workspaces`.`Name` AS `Name`,`workspaces`.`IconID` AS `IconId`,`workspacepermissions`.`IsDefault` AS `IsDefault`
From `workspaces` left join `workspacepermissions` ON (`workspaces`.`GUID` = `workspacepermissions`.`WorkspaceGUID`) left join `workspacedatatilerelations` ON (`workspaces`.`GUID` = `workspacedatatilerelations`.`WorkspaceGUID`)
Where `workspacepermissions`.`UserID` = 1 and `workspacedatatilerelations`.`WorkspaceGUID` IS NOT NULL Order by `workspacedatatilerelations`.`Order`;

This final example also runs each time a computer is opened. This query is used to load data for displaying the workspace tiles for a particular user (and this is actually made by the API; this query doesn’t originate from the desktop client itself).

The original query performed a full table scan on the workspacepermissions table (the worst case scenario for performance at scale). In this particular example the file sort is unavoidable due to the query structure, but with the addition of the index (and a 66% reduction in rows returned from the first table) it’s increasingly likely that the data set is smaller and that the temporary table will be able to fit into memory and not be written to disk. It’s also worth noting that this particular table ships with a composite index, but it’s columns are in the wrong order for this query.

Real world performance factors

It’s important to note when reviewing Database Index Packs performance that the ultimate impact will vary between Automate instances. These differences come from both application usage patterns (an index won’t be used if it’s table is never queried) along with the configuration of MySQL (indexes are increasingly used as data size increases).

Technical details

Database Index Packs are compatible with hosted and on-premise Automate servers, and are installed via a script. Indexes are created on the following tables:

agents
commands
computerconfig
computers
contacts
dependancy
eventlogs
extrafielddata
heartbeatcomputers
hotfix
h_scripts
inv_networkcard
maintenancemodequeue
mastergroups
patchapprovalsettings
plugin_screenconnect_scinstalled
processes
roledefinitions
scriptstate
searchfolders
sensorchecks
software
ticketdata
tickets
timers
userclasses
usersec
virusscanners
workspacepermissions

Getting started with Database Index Packs

Database Index Packs is a free promotional product. You can use the form below to enter into our daily drawing.