3 Formatted text
source | link

I have a table with these schemas:

 CREATE TABLE `transactions` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `description` varchar(255) NOT NULL,
  `is_deleted` tinyint(4) NOT NULL DEFAULT '0',
  `transaction_id` bigint(20) NOT NULL,
  `ref_transaction_id` bigint(20) NOT NULL,
  `action` smallint(6) NOT NULL,
  `balance` decimal(16,2) NOT NULL,
  `created` datetime NOT NULL,
  `updated` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_transaction_id` (`transaction_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 

When action = 1action = 1, ref_transaction_idref_transaction_id will be 00 When action > 1action > 1 (other values are 2,3,4,52,3,4,5), ref_transaction_idref_transaction_id will be a non-zero integer. It actually the transaction_idtransaction_id of a previous record in the same table.

I want to create index to support for this query:

select * from transactions where created>="?" and created<= "?" and ref_transaction_id=0 and action=1

select * 
from transactions 
where `created`>="?" and `created`<= "?" and `ref_transaction_id`=0 and `action`=1

I feel like a composite index like (action, ref_transaction_id, createdaction, ref_transaction_id, created) will not so efficient since my usage only check for ref_transaction_id = 0ref_transaction_id = 0 ( which is roughly 20% of data). Any better way to define composite index with condition.

I have a table with these schemas:

 CREATE TABLE `transactions` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `description` varchar(255) NOT NULL,
  `is_deleted` tinyint(4) NOT NULL DEFAULT '0',
  `transaction_id` bigint(20) NOT NULL,
  `ref_transaction_id` bigint(20) NOT NULL,
  `action` smallint(6) NOT NULL,
  `balance` decimal(16,2) NOT NULL,
  `created` datetime NOT NULL,
  `updated` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_transaction_id` (`transaction_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 

When action = 1, ref_transaction_id will be 0 When action > 1(other values are 2,3,4,5), ref_transaction_id will be a non-zero integer. It actually the transaction_id of a previous record in the same table.

I want to create index to support for this query:

select * from transactions where created>="?" and created<= "?" and ref_transaction_id=0 and action=1

I feel like a composite index like (action, ref_transaction_id, created) will not so efficient since my usage only check for ref_transaction_id = 0 ( which is roughly 20% of data). Any better way to define composite index with condition.

I have a table with these schemas:

 CREATE TABLE `transactions` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `description` varchar(255) NOT NULL,
  `is_deleted` tinyint(4) NOT NULL DEFAULT '0',
  `transaction_id` bigint(20) NOT NULL,
  `ref_transaction_id` bigint(20) NOT NULL,
  `action` smallint(6) NOT NULL,
  `balance` decimal(16,2) NOT NULL,
  `created` datetime NOT NULL,
  `updated` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_transaction_id` (`transaction_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 

When action = 1, ref_transaction_id will be 0 When action > 1 (other values are 2,3,4,5), ref_transaction_id will be a non-zero integer. It actually the transaction_id of a previous record in the same table.

I want to create index to support for this query:

select * 
from transactions 
where `created`>="?" and `created`<= "?" and `ref_transaction_id`=0 and `action`=1

I feel like a composite index like (action, ref_transaction_id, created) will not so efficient since my usage only check for ref_transaction_id = 0 ( which is roughly 20% of data). Any better way to define composite index with condition.

2 edited tags
| link
1
source | link

Define MYSQL index with condition

I have a table with these schemas:

 CREATE TABLE `transactions` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) NOT NULL,
  `description` varchar(255) NOT NULL,
  `is_deleted` tinyint(4) NOT NULL DEFAULT '0',
  `transaction_id` bigint(20) NOT NULL,
  `ref_transaction_id` bigint(20) NOT NULL,
  `action` smallint(6) NOT NULL,
  `balance` decimal(16,2) NOT NULL,
  `created` datetime NOT NULL,
  `updated` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_transaction_id` (`transaction_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 

When action = 1, ref_transaction_id will be 0 When action > 1(other values are 2,3,4,5), ref_transaction_id will be a non-zero integer. It actually the transaction_id of a previous record in the same table.

I want to create index to support for this query:

select * from transactions where created>="?" and created<= "?" and ref_transaction_id=0 and action=1

I feel like a composite index like (action, ref_transaction_id, created) will not so efficient since my usage only check for ref_transaction_id = 0 ( which is roughly 20% of data). Any better way to define composite index with condition.