0

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.

1

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.
1

For

where `created`>= "?"
  and `created`<= "?"
  and `ref_transaction_id`=0
  and `action`=1

The optimal index is

INDEX(ref_transaction_id, action,  -- in either order
      created)

All the relevant rows with be adjacent in the index.

It does not matter what values ref_transaction_id and action; it does not matter that they are correlated; it does not matter what the 'cardinality' of each is. What matters is that you are testing both of them with =

If you remove ref_transaction_id from the WHERE clause, then you need to remove it from the index (or have another index). Else, the Optimizer will assume that there could be multiple different values for that column and not use all of the index columns.

May I suggest this coding pattern:

where `created` >= ?
  and `created`  < ? + INTERVAL 7 DAY

if, for example, the range is one week. Also, both ? will take on the same date, no need for you to play with leap year, end of year, 31 days in some months, etc.

Are there multiple rows for a single transaction_id? If not, why not make it the PRIMARY KEY and get rid of id?

  • Thanks for your suggestion. In the end, I choose (action, created) as composite index. – aladine Aug 31 '18 at 14:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.