53zewaygsre
New Member
I have the talbe like that:\[code\]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`))\[/code\]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:\[code\]SELECT * FROM UserTrans WHERE transaction_id = '212398043' AND source = 'COMPANY_A';\[/code\]this query getting very slow, like 2 seconds to run now. Should I index the transaction_id and the source?
e.g. KEY \[code\]join_id\[/code\] (\[code\]transaction_id\[/code\], \[code\]source\[/code\])What is the drawback if i do that?
e.g. KEY \[code\]join_id\[/code\] (\[code\]transaction_id\[/code\], \[code\]source\[/code\])What is the drawback if i do that?