MySQL: What are the drawbacks of indexing an additional field?

Question

I have the talbe like that:

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.

The transaction_id is var because sometimes it can be aphanumeric.

the id is the primary key.

so.. here is the thing, I have over 1M records. However, there is a query to check for duplicate transaciton_id on the specified source. So, here is my query:

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

this query getting very slow, like 2 seconds to run now. Should I index the transaction_id and the source?
e.g. KEY join_id (transaction_id, source)

What is the drawback if i do that?

Answer 1

The main drawback is that the new index will take up space on your disks. It will also make inserts and updates a little bit slower (but this is often negligible in most situations).

On the other hand, your query will probably run in just a few milliseconds instead of 2 seconds.

Answer 2

The drawbacks to adding indices are space (since storing indexes does take up space) and insert time (since when you insert new records, they have to be added to the indices).

That said, you may not need to index both fields - just indexing one of them may be enough.

Answer 3

Obviously the benefit is that it will improve the performance of certain queries.

The drawback is that it will take a bit of space to store the index and a bit of work for the RDBMS to maintain the index. The index is especially prone to consume space because your transaction_id is such a wide string.

You might consider whether transaction_id really needs to be up to 255 characters long, or if you could declare its max length to be something shorter.

Or you could use a prefix index to index only the first n characters:

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

---

@Daniel has a good point that you might get the same benefit and save even more space by indexing only one column. Since you're doing SELECT * you've ruled out the benefit of a covering index.

Also if you intend transaction_id to be unique, why not constrain it to be unique?

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

Answer 4

I would think about diching your id column and use transaction_id as your primary key I am assuming that transaction_id is unique.

this will mean that your schema prevents you from inserting a transaction id that is already there.

this reduces the the amount of data being stored, and also reduces the number of columns needing to be indexed.

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

your current schema allows you to put in duplicates, which is an unnecessary evil.