)]}'
{"heat/db/sqlalchemy/migrate_repo/versions/029_add_indexes_to_resource.py":[{"author":{"_account_id":6488,"name":"Clint Byrum","email":"clint@fewbar.com","username":"clint-fewbar"},"change_message_id":"672eb569f0893cce8e074750f3da2f7d5de44ff8","unresolved":false,"context_lines":[{"line_number":22,"context_line":"    table \u003d Table(\u0027resource\u0027, meta, autoload\u003dTrue)"},{"line_number":23,"context_line":""},{"line_number":24,"context_line":"    cols \u003d [\u0027name\u0027, \u0027nova_instance\u0027]"},{"line_number":25,"context_line":"    for col in cols:"},{"line_number":26,"context_line":"        index \u003d Index(\u0027resource_%s_idx\u0027 % col, getattr(table.c, col))"},{"line_number":27,"context_line":"        index.create(migrate_engine)"},{"line_number":28,"context_line":""}],"source_content_type":"text/x-python","patch_set":3,"id":"AAAATn%2F%2ByA0%3D","line":25,"updated":"2013-11-20 09:42:22.000000000","message":"not a -1 thing, but this can be expressed with one line and with a tuple:\n\n    for col in (\u0027name\u0027, \u0027nova_instance\u0027):","commit_id":"f15a8c207a44fefb20540f3aae28e4b35147334c"},{"author":{"_account_id":6488,"name":"Clint Byrum","email":"clint@fewbar.com","username":"clint-fewbar"},"change_message_id":"672eb569f0893cce8e074750f3da2f7d5de44ff8","unresolved":false,"context_lines":[{"line_number":34,"context_line":"    table \u003d Table(\u0027resource\u0027, meta, autoload\u003dTrue)"},{"line_number":35,"context_line":""},{"line_number":36,"context_line":"    cols \u003d [\u0027name\u0027, \u0027nova_instance\u0027]"},{"line_number":37,"context_line":"    for col in cols:"},{"line_number":38,"context_line":"        index \u003d Index(\u0027resource_%s_idx\u0027 % col, getattr(table.c, col))"},{"line_number":39,"context_line":"        index.drop(migrate_engine)"}],"source_content_type":"text/x-python","patch_set":3,"id":"AAAATn%2F%2ByAU%3D","line":37,"updated":"2013-11-20 09:42:22.000000000","message":"same here.","commit_id":"f15a8c207a44fefb20540f3aae28e4b35147334c"}],"heat/db/sqlalchemy/migrate_repo/versions/030_add_indexes_to_stack.py":[{"author":{"_account_id":7256,"name":"Randall Burt","email":"randall.burt@rackspace.com","username":"randall-burt"},"change_message_id":"58716aaba5838f02ead18a0ae38e0eb1df1738c9","unresolved":false,"context_lines":[{"line_number":22,"context_line":"    table \u003d Table(\u0027stack\u0027, meta, autoload\u003dTrue)"},{"line_number":23,"context_line":""},{"line_number":24,"context_line":"    cols \u003d [\u0027created_at\u0027, \u0027deleted_at\u0027, \u0027name\u0027, \u0027owner_id\u0027,"},{"line_number":25,"context_line":"            \u0027status\u0027, \u0027tenant\u0027, \u0027updated_at\u0027]"},{"line_number":26,"context_line":"    for col in cols:"},{"line_number":27,"context_line":"        index \u003d Index(\u0027stack_%s_idx\u0027 % col, getattr(table.c, col))"},{"line_number":28,"context_line":"        index.create(migrate_engine)"}],"source_content_type":"text/x-python","patch_set":3,"id":"AAAATn%2F%2B0N4%3D","line":25,"updated":"2013-11-19 19:23:46.000000000","message":"I\u0027m not seeing where we query on status or updated_at. I\u0027d prefer we avoid index overhead for things we don\u0027t query regularly. I think its better to index for the default and perhaps handle these other \"obvious\" sorting columns in the proposed change that explicitly documents/limits sorting columns.","commit_id":"f15a8c207a44fefb20540f3aae28e4b35147334c"},{"author":{"_account_id":7714,"name":"Tim Schnell","email":"tim.schnell@rackspace.com","username":"timductive"},"change_message_id":"4bf6c50bc20d81fa7a300c7efdf55a683a237d92","unresolved":false,"context_lines":[{"line_number":22,"context_line":"    table \u003d Table(\u0027stack\u0027, meta, autoload\u003dTrue)"},{"line_number":23,"context_line":""},{"line_number":24,"context_line":"    cols \u003d [\u0027created_at\u0027, \u0027deleted_at\u0027, \u0027name\u0027, \u0027owner_id\u0027,"},{"line_number":25,"context_line":"            \u0027status\u0027, \u0027tenant\u0027, \u0027updated_at\u0027]"},{"line_number":26,"context_line":"    for col in cols:"},{"line_number":27,"context_line":"        index \u003d Index(\u0027stack_%s_idx\u0027 % col, getattr(table.c, col))"},{"line_number":28,"context_line":"        index.create(migrate_engine)"}],"source_content_type":"text/x-python","patch_set":3,"id":"AAAATn%2F%2B0Bc%3D","line":25,"in_reply_to":"AAAATn%2F%2B0N4%3D","updated":"2013-11-19 19:56:18.000000000","message":"Agree with Randall on this. Just remove status and updated_at from indexing until a patch that queries against them.","commit_id":"f15a8c207a44fefb20540f3aae28e4b35147334c"},{"author":{"_account_id":6488,"name":"Clint Byrum","email":"clint@fewbar.com","username":"clint-fewbar"},"change_message_id":"89b1978582a49a8c99def4f0836be4852f50714a","unresolved":false,"context_lines":[{"line_number":25,"context_line":"            \u0027status\u0027, \u0027tenant\u0027, \u0027updated_at\u0027]"},{"line_number":26,"context_line":"    for col in cols:"},{"line_number":27,"context_line":"        index \u003d Index(\u0027stack_%s_idx\u0027 % col, getattr(table.c, col))"},{"line_number":28,"context_line":"        index.create(migrate_engine)"},{"line_number":29,"context_line":""},{"line_number":30,"context_line":""},{"line_number":31,"context_line":"def downgrade(migrate_engine):"}],"source_content_type":"text/x-python","patch_set":3,"id":"AAAATn%2F%2Brh4%3D","line":28,"updated":"2013-11-21 16:48:03.000000000","message":"It just struck me that these are not actually useful indexes, and that using them for sorting will likely be difficult anyway.\n\nWhile MySQL can make use of two indexes in a query, that is only for lookups. It is called a merge index, and often it performs worse than just using the best cardinality index and then scanning the rest.\n\nSince 99% of the queries we have are tenant scoped, as such:\n\nSELECT * FROM stack WHERE tenant\u003d\u0027xxxxx\u0027 ORDER BY \u0027status\u0027;\n\nYou will not actually be able to use the status index. Note this explain (I created the tenant and status indexes manually as tenant_idx and stack_status_idx):\n\n    mysql\u003e explain SELECT * FROM stack WHERE tenant \u003d \u00274956c533154c476799c688eda7ed65ab\u0027 ORDER BY status\\G\n    *************************** 1. row ***************************\n               id: 1\n      select_type: SIMPLE\n            table: stack\n             type: ref\n    possible_keys: tenant_idx\n              key: tenant_idx\n          key_len: 259\n              ref: const\n             rows: 6\n            Extra: Using where; Using filesort\n    1 row in set (0.00 sec)\n\nNote that only tenant_idx is possible. The only way this helps with sorting is with a compound index on tenant + status:\n\n    mysql\u003e explain SELECT * FROM stack WHERE tenant \u003d \u00274956c533154c476799c688eda7ed65ab\u0027 ORDER BY status\\G\n    *************************** 1. row ***************************\n               id: 1\n      select_type: SIMPLE\n            table: stack\n             type: ref\n    possible_keys: tenant_idx,stack_tenant_status_idx\n              key: stack_tenant_status_idx\n          key_len: 259\n              ref: const\n             rows: 6\n            Extra: Using where\n    1 row in set (0.00 sec)\n\nSo I recommend not merging this at all. Making these compound indexes of tenant+others will make the indexes huge (tenant is varchar(256), many of these are also varchar(255)). I suggest just having a tenant index, which will reduce the impact of the most common queries with minimal impact to the write load.","commit_id":"f15a8c207a44fefb20540f3aae28e4b35147334c"},{"author":{"_account_id":7256,"name":"Randall Burt","email":"randall.burt@rackspace.com","username":"randall-burt"},"change_message_id":"a6efc91ff312ae774f9407ecaf78dc2e255f9fe4","unresolved":false,"context_lines":[{"line_number":21,"context_line":""},{"line_number":22,"context_line":"    table \u003d Table(\u0027stack\u0027, meta, autoload\u003dTrue)"},{"line_number":23,"context_line":""},{"line_number":24,"context_line":"    cols \u003d [\u0027created_at\u0027, \u0027deleted_at\u0027, \u0027name\u0027, \u0027owner_id\u0027, \u0027tenant\u0027]"},{"line_number":25,"context_line":"    for col in cols:"},{"line_number":26,"context_line":"        index \u003d Index(\u0027stack_%s_idx\u0027 % col, getattr(table.c, col))"},{"line_number":27,"context_line":"        index.create(migrate_engine)"}],"source_content_type":"text/x-python","patch_set":4,"id":"AAAATn%2F%2BlK4%3D","line":24,"updated":"2013-11-22 20:50:19.000000000","message":"See Clint\u0027s comments on the last patch. Seems like these indexes don\u0027t buy us much except the one on tenant; though if we ever get the management/keystone stuff sorted, owner_id and/or name may come in handy.","commit_id":"faaa72903d8e09369332b581c9cf92288f008032"}],"heat/db/sqlalchemy/migrate_repo/versions/031_add_index_to_resource_data.py":[{"author":{"_account_id":7256,"name":"Randall Burt","email":"randall.burt@rackspace.com","username":"randall-burt"},"change_message_id":"58716aaba5838f02ead18a0ae38e0eb1df1738c9","unresolved":false,"context_lines":[{"line_number":20,"context_line":"    meta.bind \u003d migrate_engine"},{"line_number":21,"context_line":""},{"line_number":22,"context_line":"    table \u003d Table(\u0027resource_data\u0027, meta, autoload\u003dTrue)"},{"line_number":23,"context_line":"    index \u003d Index(\u0027resource_data_key_idx\u0027, table.c.key)"},{"line_number":24,"context_line":"    index.create(migrate_engine)"},{"line_number":25,"context_line":""},{"line_number":26,"context_line":""}],"source_content_type":"text/x-python","patch_set":3,"id":"AAAATn%2F%2B0NY%3D","line":23,"updated":"2013-11-19 19:23:46.000000000","message":"Shouldn\u0027t this index by default since its a FK?","commit_id":"f15a8c207a44fefb20540f3aae28e4b35147334c"},{"author":{"_account_id":6488,"name":"Clint Byrum","email":"clint@fewbar.com","username":"clint-fewbar"},"change_message_id":"672eb569f0893cce8e074750f3da2f7d5de44ff8","unresolved":false,"context_lines":[{"line_number":20,"context_line":"    meta.bind \u003d migrate_engine"},{"line_number":21,"context_line":""},{"line_number":22,"context_line":"    table \u003d Table(\u0027resource_data\u0027, meta, autoload\u003dTrue)"},{"line_number":23,"context_line":"    index \u003d Index(\u0027resource_data_key_idx\u0027, table.c.key)"},{"line_number":24,"context_line":"    index.create(migrate_engine)"},{"line_number":25,"context_line":""},{"line_number":26,"context_line":""}],"source_content_type":"text/x-python","patch_set":3,"id":"AAAATn%2F%2ByAE%3D","line":23,"in_reply_to":"AAAATn%2F%2B0Dc%3D","updated":"2013-11-20 09:42:22.000000000","message":"Actually in MySQL FK targets such as resource_data.key will automatically have indexes. Otherwise they would be horribly inefficient. However, it is better to be explicit, as there won\u0027t be any duplication, the silently created fk index will be dropped in favor of an explicitly created one:\n\nhttp://dev.mysql.com/doc/refman/5.5/en/create-table-foreign-keys.html\n\n\"MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later, if you create another index that can be used to enforce the foreign key constraint. index_name, if given, is used as described previously.\"\n\nI do not know how postgres would handle this situation.","commit_id":"f15a8c207a44fefb20540f3aae28e4b35147334c"},{"author":{"_account_id":7714,"name":"Tim Schnell","email":"tim.schnell@rackspace.com","username":"timductive"},"change_message_id":"4bf6c50bc20d81fa7a300c7efdf55a683a237d92","unresolved":false,"context_lines":[{"line_number":20,"context_line":"    meta.bind \u003d migrate_engine"},{"line_number":21,"context_line":""},{"line_number":22,"context_line":"    table \u003d Table(\u0027resource_data\u0027, meta, autoload\u003dTrue)"},{"line_number":23,"context_line":"    index \u003d Index(\u0027resource_data_key_idx\u0027, table.c.key)"},{"line_number":24,"context_line":"    index.create(migrate_engine)"},{"line_number":25,"context_line":""},{"line_number":26,"context_line":""}],"source_content_type":"text/x-python","patch_set":3,"id":"AAAATn%2F%2B0Dc%3D","line":23,"in_reply_to":"AAAATn%2F%2B0NY%3D","updated":"2013-11-19 19:56:18.000000000","message":"No, in sqlalchemy you have to explicitly define all of the indexes.","commit_id":"f15a8c207a44fefb20540f3aae28e4b35147334c"}]}