From d4a505fd30aacfac933013eac17ea40256b72d90 Mon Sep 17 00:00:00 2001 From: Ted Trask Date: Thu, 9 Dec 2010 14:51:51 +0000 Subject: Added indexes to try to help query speed --- provisioning-model.lua | 21 ++++++++++++++++++++- 1 file changed, 20 insertions(+), 1 deletion(-) (limited to 'provisioning-model.lua') diff --git a/provisioning-model.lua b/provisioning-model.lua index 074d253..7b92189 100644 --- a/provisioning-model.lua +++ b/provisioning-model.lua @@ -27,42 +27,61 @@ local table_creation_scripts = { -- List of each device that we manage provisioning_devices = { "CREATE TABLE provisioning_devices (device_id SERIAL PRIMARY KEY, name VARCHAR(255) UNIQUE, label VARCHAR(255), template_id INTEGER)", + "CREATE INDEX devices_name_idx ON provisioning_devices (name)", + "CREATE INDEX devices_template_idx ON provisioning_devices (template_id)", }, -- Multi-to-multi mapping of devices to classes -- Need to enforce that devices do not contain more than one class with same name (using triggers) devices_to_classes = { "CREATE TABLE devices_to_classes (device_id INTEGER, class_id INTEGER)", + "CREATE INDEX d2c_device_idx ON devices_to_classes (device_id)", + "CREATE INDEX d2c_class_idx ON devices_to_classes (class_id)", }, -- List of different device classes --- Need to enforce that name/label combo is unique provisioning_classes = { "CREATE TABLE provisioning_classes (class_id SERIAL PRIMARY KEY, name VARCHAR(255), label VARCHAR(255))", + "CREATE INDEX classes_name_idx ON provisioning_classes (name)", + "CREATE INDEX classes_label_idx ON provisioning_classes (label)", + -- Need to enforce that name/label combo is unique + "CREATE UNIQUE INDEX classes_name_label_idx ON provisioning_classes (name, label)", }, -- Multi-to-multi mapping of classes to parameter groups -- Need to enforce that classes do not contain more than one parameter group with same name (using triggers) classes_to_param_groups = { "CREATE TABLE classes_to_param_groups (class_id INTEGER, group_id INTEGER)", + "CREATE INDEX c2g_class_idx ON classes_to_param_groups (class_id)", + "CREATE INDEX c2g_group_idx ON classes_to_param_groups (group_id)", }, -- List of different parameter groups provisioning_groups = { "CREATE TABLE provisioning_groups (group_id SERIAL PRIMARY KEY, name VARCHAR(255), label VARCHAR(255) UNIQUE, seq INTEGER)", + "CREATE INDEX groups_name_idx ON provisioning_groups (name)", }, -- Multi-to-multi mapping of parameter groups to parameters param_groups_to_params = { "CREATE TABLE param_groups_to_params (group_id INTEGER, param_id INTEGER, value VARCHAR(255), editable BOOLEAN)", + "CREATE INDEX g2p_group_idx ON param_groups_to_params (group_id)", + "CREATE INDEX g2p_param_idx ON param_groups_to_params (param_id)", + "CREATE INDEX g2p_editable_idx ON param_groups_to_params (editable)", }, -- List of each parameter used in any way for any device - mostly for how to display provisioning_params = { "CREATE TABLE provisioning_params (param_id SERIAL PRIMARY KEY, name VARCHAR(255) UNIQUE, type VARCHAR(255), label VARCHAR(255), descr VARCHAR(255), value VARCHAR(255), seq INTEGER, regexp VARCHAR(255))", + "CREATE INDEX params_name_idx ON provisioning_params (name)", }, -- All of the (non-default) parameter values for all devices are stored here provisioning_values = { -- device_id is a device id from provisioning_devices and param_id is a param id from provisioning_params "CREATE TABLE provisioning_values (device_id INTEGER, group_name VARCHAR(255), param_id INTEGER, value VARCHAR(255))", + "CREATE INDEX values_device_idx ON provisioning_values (device_id)", + "CREATE INDEX values_group_idx ON provisioning_values (group_name)", + "CREATE INDEX values_param_idx ON provisioning_values (param_id)", + "CREATE INDEX values_device_group_param_idx ON provisioning_values (device_id, group_name, param_id)", }, -- List of options for parameters provisioning_options = { "CREATE TABLE provisioning_options (param_id INTEGER, label VARCHAR(255), value VARCHAR(255), seq INTEGER)", + "CREATE INDEX options_param_idx ON provisioning_options (param_id)", }, -- Where do we define the triggers to enforce the foreign keys? Triggers are only on updates, so don't have to worry about read speed -- Need to define indices -- cgit v1.2.3