[
https://issues.apache.org/jira/browse/CLOUDSTACK-9340?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=15234140#comment-15234140
]
ASF GitHub Bot commented on CLOUDSTACK-9340:
--------------------------------------------
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;
> General DB Optimization
> -----------------------
>
> Key: CLOUDSTACK-9340
> URL: https://issues.apache.org/jira/browse/CLOUDSTACK-9340
> Project: CloudStack
> Issue Type: Improvement
> Security Level: Public(Anyone can view this level - this is the
> default.)
> Affects Versions: 4.9.0
> Reporter: Nicolas Vazquez
> Labels: database
> Fix For: 4.9.0
>
>
> h2. General DB Optimization
> In some production environments there were being experimented delays in most
> of the jobs. A search for DB optimization was taken and some deficiencies
> were discovered, we can group them in 4 groups:
> * Incorrect PRIMARY key
> * Duplicate PRIMARY KEY
> * Missing indexes (Add indexes to avoid full table scans)
> * Some views query (Change view to improve account retrieval speed)
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)
