Author: justi9 Date: 2008-01-14 11:58:29 -0500 (Mon, 14 Jan 2008) New Revision: 1565 Added: mgmt/mint/python/mint/indexes.sql Modified: mgmt/mint/Makefile mgmt/mint/python/mint/schema.sql Log: Adds some indexes to foreign key columns on q,e,c and their stats objects. This results in big query time improvements. Modified: mgmt/mint/Makefile =================================================================== --- mgmt/mint/Makefile 2008-01-14 15:55:39 UTC (rev 1564) +++ mgmt/mint/Makefile 2008-01-14 16:58:29 UTC (rev 1565) @@ -17,3 +17,4 @@ schema-sql: python/mint/schema.py sqlobject-admin sql -m mint -m mint.schema -c ${dsn} | sed -e '1,2d' > python/mint/schema.sql + cat python/mint/indexes.sql >> python/mint/schema.sql Added: mgmt/mint/python/mint/indexes.sql =================================================================== --- mgmt/mint/python/mint/indexes.sql (rev 0) +++ mgmt/mint/python/mint/indexes.sql 2008-01-14 16:58:29 UTC (rev 1565) @@ -0,0 +1,7 @@ +create index queue_vhost_id_idx on queue (vhost_id); +create index exchange_vhost_id_idx on exchange (vhost_id); +create index client_vhost_id_idx on client (vhost_id); + +create index queue_stats_queue_id_idx on queue_stats (queue_id); +create index exchange_stats_exchange_id_idx on exchange_stats (exchange_id); +create index client_stats_client_id_idx on client_stats (client_id); Modified: mgmt/mint/python/mint/schema.sql =================================================================== --- mgmt/mint/python/mint/schema.sql 2008-01-14 15:55:39 UTC (rev 1564) +++ mgmt/mint/python/mint/schema.sql 2008-01-14 16:58:29 UTC (rev 1565) @@ -458,3 +458,10 @@ ALTER TABLE vhost_stats ADD CONSTRAINT vhost_id_exists FOREIGN KEY (vhost_id) REFERENCES vhost (id) ON DELETE SET NULL; +create index queue_vhost_id_idx on queue (vhost_id); +create index exchange_vhost_id_idx on exchange (vhost_id); +create index client_vhost_id_idx on client (vhost_id); + +create index queue_stats_queue_id_idx on queue_stats (queue_id); +create index exchange_stats_exchange_id_idx on exchange_stats (exchange_id); +create index client_stats_client_id_idx on client_stats (client_id);