cloudstack-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From nvazquez <...@git.apache.org>
Subject [GitHub] cloudstack pull request: CLOUDSTACK-9340: General DB Optimization
Date Sun, 10 Apr 2016 14:36:25 GMT
Github user nvazquez commented on the pull request:

    https://github.com/apache/cloudstack/pull/1466#issuecomment-207994438
  
    @bhaisaab Most of the indexes being dropped are duplicate. In create-schema.sql quite
a lot of tables are created like this:
    CREATE TABLE `cloud`.`version` (
    `id` bigint unsigned NOT NULL UNIQUE AUTO_INCREMENT COMMENT 'id',
    `version` char(40) NOT NULL UNIQUE COMMENT 'version',
    `updated` datetime NOT NULL COMMENT 'Date this version table was updated',
    `step` char(32) NOT NULL COMMENT 'Step in the upgrade to this version',
    PRIMARY KEY (`id`),
    INDEX `i_version__version`(`version`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    
    When column ID is marked as UNIQUE MySQL creates an unique index ID. Then below a primary
key ID is defined. Primary key in its nature already guarantees uniqueness so it is safe to
drop index ID leaving only primary key to remain.
    There are more than 50 tables like this plus 3 more tables where unique index based on
ID column was created twice. For example for domain_router table one duplicate index was created
initially in create-schema.sql and another duplicate was generated in schema-442to450.sql
when the following line was executed:
    ALTER TABLE `cloud`.`domain_router` MODIFY `id` bigint unsigned AUTO_INCREMENT UNIQUE
NOT NULL;


---
If your project is set up for it, you can reply to this email and have your
reply appear on GitHub as well. If your project does not have this feature
enabled and wishes so, or if the feature is enabled but not working, please
contact infrastructure at infrastructure@apache.org or file a JIRA ticket
with INFRA.
---

Mime
View raw message