CODEWITHSUNDEEP
mysql
Sujay DSa
Sujay DSa

Reputation: 1192

Improving select statement performance

I have a table with around 500,000 rows, with a composite primary key index. I'm trying a simple select statement as follows

select * from transactions where account_id='1' and transaction_id='003a4955acdbcf72b5909f122f84d';

The explain statement gives me this

id | select_type | table        | partitions | type  | possible_keys | key     | key_len | ref         | rows | filtered | Extra
-------------------------------------------------------------------------------------------------------------------------------------
1 | SIMPLE       | transactions | NULL       | const | PRIMARY       | PRIMARY | 94      | const,const | 1    | 100.00    | NULL

My primary index is on account_id and transaction_id. My engine is InnoDB.

When I run my query it takes around 156 milliseconds.

Given that explain shows that only one row needs to be examined, I'm not sure how to optimize this further. What changes can I do to significantly improve this?

Upvotes: 0

Views: 47

Answers (1)

Cynical
Cynical

Reputation: 9568

I'm going to speculate a bit, given the information provided: your primary key is composed of an integer field account_id and a varchar one called transaction_id.

Since they're both components of the PRIMARY index created when you defined them as PRIMARY KEY(account_id, transaction_id), as they are they're the best you can have.

I think the bottleneck here is the transaction_id: as a string, it requires more effort to be indexed, and to be searched for. Changing its type to a different, easier to search one (i.e. numbers) would probably help.

The only other improvement I see is to simplify the PRIMARY KEY itself, either by removing the account_id field (it seems useless to me, since the transaction_id tastes like being unique, but that depends on your design) or by substitute the whole key with an integer, AUTO INCREMENT value (not recommended).

Upvotes: 2

Related Questions