MySQL: What are the disadvantages of indexing an extra field?

Question

I have such a talbe:

CREATE TABLE  UserTrans (
 `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` int(10) unsigned NOT NULL,
   `transaction_id` varchar(255) NOT NULL default '0',
  `source` varchar(100) NOT NULL,
   PRIMARY KEY (`id`),
   KEY `user_id` (`user_id`)
)

with innodb engine.

Transaction transaction_id is var, because sometimes it can be aphanumeric.

An identifier is a primary key.

so .. that’s what, I have more than 1 M records. However, there is a request to check the duplicate transaciton_id for the specified source. So here is my query:

SELECT * 
  FROM UserTrans 
 WHERE transaction_id = '212398043' 
   AND source = 'COMPANY_A';

this request becomes very slow, for example, 2 seconds to run. Should I index transaction_id and source?
e.g. KEY join_id( transaction_id, source)

What is the disadvantage if I do this?

Answer 1

, , .

, , . , transaction_id .

, 255 , - .

n :

CREATE INDEX join_id ON UserTrans (transaction_id(16), source(16));

---

@Daniel , ​​ , . SELECT *, .

, , transaction_id , ?

CREATE UNIQE INDEX uq_transaction_id ON UserTrans (transaction_id(16));

Answer 2

, . ( ).

, , , 2 .

Answer 3

- ( ) (, , ).

, - .

Answer 4

, , transaction_id .

, , .

this reduces the amount of data stored and also reduces the number of columns that need to be indexed.

if the source company and transaction_id are a composite key. I would make two columns the primary key.

your current scheme allows you to enter duplicates, which is an unnecessary evil.