ceilometer resource table columns are not indexed

[Ashwin Agate]

resource table has indexes only on internal_id and resource_id columns

mysql> show indexes from resource;
+----------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| resource | 0 | PRIMARY | 1 | internal_id | A | 2 | NULL | NULL | | BTREE | | |
| resource | 1 | ix_resource_resource_id | 1 | resource_id | A | 10 | NULL | NULL | YES | BTREE | | |
+----------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

user_id, project_id, source_id and metadata_hash columns are not indexed.

Its important to have all the columns indexed. metadata_hash is looked up at every insert, and others can be used by various queries using project_id, user_id, source_id as query parameters.

See related bugs on creating unique index on those columns https://bugs.launchpad.net/ceilometer/+bug/1419271 and https://bugs.launchpad.net/ceilometer/+bug/1353039. where unique indexes could not be created (due to large data size)

We should still index individual user_id, project_id, source_id and metadata_hash columns.

[OpenStack Infra (hudson-openstack)]

Fix proposed to branch: master
Review: https://review.openstack.org/217453

[OpenStack Infra (hudson-openstack)]

Reviewed: https://review.openstack.org/217453
Committed: https://git.openstack.org/cgit/openstack/ceilometer/commit/?id=4b739eeb66d1409537d29eb6c9f584d1dae725a1
Submitter: Jenkins
Branch: master

commit 4b739eeb66d1409537d29eb6c9f584d1dae725a1
Author: Ashwin Agate <email address hidden>
Date: Thu Aug 27 00:27:02 2015 +0000

    Add index to metadata_hash column of resource table

    Resource table's metadata_hash column is not indexed.

    Adding index on metadata_hash column reduces number
    of rows scanned to one. That is if a resource
    (same resource_id) has say n rows in resource table
    (due to difference in metadata), with additional index
    only one row will have to be scanned.

    Change-Id: I2a99bdac88f5fa14ac5f82497d1a12196419ae68
    Closes-Bug: #1488544