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
action = 1,
ref_transaction_id will be
action > 1 (other values are
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.