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 &gt; 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`&gt;="?" and `created`&lt;= "?" 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.
Sounds like:


if action=1, then ref_transaction_id can only be 0, and
if ref_transaction_id=0, the action can only be 1


If that's correct, then the query may be simplified:

select * 
from transactions 
where `created` between "?" and "?" and `action`=1;


Notes:


add an index for action to help the query
use between to range check the date; suspect minor performance improvement




Unsolicited Observations

This sounds like a linked list (ref_transaction_ids) and a state machine (action). If that's true, or close, may want to consider putting that info into a separate "association" table, 

....
`fk_id` bigint(20) NOT NULL,
`transaction_id` bigint(20) NOT NULL,
`fk_prior_id` bigint(20) NOT NULL COMMENT "replaces use of ref_transaction_id",
`action` smallint(6) NOT NULL,
....


Notes:


introduces fk_prior_id as a way of finding the ref_transaction_id using a field which is already indexed, id
consider adding the date fields depending on the 
nature of the data
consider defining action as an enum to capture the state and improve readability; still stored as an integer internally but may be referenced using the defined "states" in queries.