What is the fun of merging indexes in MySql and what does it affect?

Question

MySQL

It's time to find out the secret.
I would like to fully understand how to build indexes correctly.

For example, two requests.

SELECT * FROM contacts_history ch WHERE ch.owner_id = 17 AND ch.organization_id = 1;
SELECT * FROM contacts_history ch WHERE ch.owner_id = 17;

Whether to create multiple variants of indexes depending on the condition.

I'll open the curtain.
There is a label in which a number of fields participate in where, depending on a situation.
Does this mean that for each option you need to create your own index?

And correctly I understand that the presence of a large set (options) of indexes entails a performance loss when inserting a new record into the table?

Current situation

spoiler

CREATE TABLE `contacts_history` (
  `id` BIGINT(19) NOT NULL AUTO_INCREMENT,
  `contact_id` BIGINT(19) NULL DEFAULT NULL,
  `owner_id` INT(10) NULL DEFAULT NULL,
  `creator_id` INT(10) NULL DEFAULT NULL,
  `organization_id` INT(10) NULL DEFAULT NULL,
  `project_id` BIGINT(19) NULL DEFAULT NULL,
  `status_id` INT(10) NULL DEFAULT NULL,
  `session_start_time` DATETIME NULL DEFAULT NULL,
  `session_end_time` DATETIME NULL DEFAULT NULL,
  `call_start_time` DATETIME NULL DEFAULT NULL,
  `call_end_time` DATETIME NULL DEFAULT NULL,
  `direction` TINYINT(3) NULL DEFAULT NULL COMMENT '(DC2Type:contact_call_direction_type)',
  `originator` TINYINT(3) NULL DEFAULT NULL COMMENT '(DC2Type:contact_call_originator_type)',
  `cause` TINYINT(3) NULL DEFAULT '0' COMMENT '(DC2Type:contact_call_cause_type)',
  `target` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
  `comment` LONGTEXT NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
  `status_result` VARCHAR(100) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
  `status_color` VARCHAR(9) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
  `type` TINYINT(3) NULL DEFAULT NULL COMMENT '(DC2Type:contact_history_type)',
  `created_at` DATETIME NULL DEFAULT NULL,
  `audio_recording_id` VARCHAR(36) NULL DEFAULT NULL COLLATE 'utf8mb4_unicode_ci',
  `executive_action` TINYINT(1) NULL DEFAULT '0',
  `executive_webhook` TINYINT(1) NULL DEFAULT '0',
  `user_group_id` INT(10) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `IDX_AD127C16E7A1254A` (`contact_id`) USING BTREE,
  INDEX `IDX_AD127C167E3C61F9` (`owner_id`) USING BTREE,
  INDEX `IDX_AD127C1661220EA6` (`creator_id`) USING BTREE,
  INDEX `IDX_AD127C1632C8A3DE` (`organization_id`) USING BTREE,
  INDEX `IDX_AD127C16166D1F9C` (`project_id`) USING BTREE,
  INDEX `IDX_AD127C166BF700BD` (`status_id`) USING BTREE,
  INDEX `IDX_AD127C161ED93D47` (`user_group_id`) USING BTREE,
  INDEX `IDX_AD127C1632C8A3DEE7A1254A61220EA61ED93D478B8E8428` (`organization_id`, `contact_id`, `creator_id`, `user_group_id`, `created_at`) USING BTREE,
  INDEX `owner_id_organization_id` (`owner_id`, `organization_id`) USING BTREE,
  CONSTRAINT `FK_AD127C16166D1F9C` FOREIGN KEY (`project_id`) REFERENCES `rmok`.`projects` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT `FK_AD127C161ED93D47` FOREIGN KEY (`user_group_id`) REFERENCES `rmok`.`users_group` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT `FK_AD127C1632C8A3DE` FOREIGN KEY (`organization_id`) REFERENCES `rmok`.`organizations` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT `FK_AD127C1661220EA6` FOREIGN KEY (`creator_id`) REFERENCES `rmok`.`users` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT `FK_AD127C166BF700BD` FOREIGN KEY (`status_id`) REFERENCES `rmok`.`statuses` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT `FK_AD127C167E3C61F9` FOREIGN KEY (`owner_id`) REFERENCES `rmok`.`users` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT `FK_AD127C16E7A1254A` FOREIGN KEY (`contact_id`) REFERENCES `rmok`.`contacts` (`id`) ON UPDATE NO ACTION ON DELETE NO ACTION
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
AUTO_INCREMENT=742972
;

Everything that is in the indexes is used in the selection conditions.

Pay attention to the index IDX_AD127C1632C8A3DEE7A1254A61220EA61ED93D478B8E8428
Does this mean that if the condition maximally matches the index, I will get better performance?

Answer 1

It all depends on the specific situation. For example, in your case, a composite index on the fields (`owner_id`, `organization_id`) will be used in both queries.
Accordingly, there is no need for separate indexes.

Note that if you have a composite index on (c1, c2, c3), you will have indexed search capabilities on one of the following column combinations:

SELECT    *
FROM    table_name
WHERE    c1 = v1;

SELECT    *
FROM    table_name
WHERE    c1 = v1 AND c2 = v2;

SELECT      *
FROM    table_name
WHERE    c1 = v1 AND c2 = v2 AND c3 = v3;

It is important to keep in mind that each index slows down the add and update operations and does not always help with selects.
EXPLAIN to help you. And may ACID be with you.

Answer 2

The situation when a double index becomes noticeably more efficient than two separate ones in terms of reading will come much later, when there will be a lot of data (and we are talking about really huge data) ...
The config and hardware on which everything is launched also have a greater influence, and for example transition from hdd to ssd (by the way, even if you put the indexes separately on ssd).
Units per second of records is about nothing a load for linear data (I repeat, if the hardware doesn’t completely suck, since I saw a situation when the database was bent from a couple of dozen records to an ancient hdd)