Q:

What is better: select duplicates check OR making a unique index?

I have a table like this:
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `torderid` int(11) DEFAULT NULL,
  `tuid` int(11) DEFAULT NULL,
  `tcontid` int(11) DEFAULT NULL,
  `tstatus` varchar(10) DEFAULT 'pending',

the rule here is that the same user UID can't have more than one pending order with the same contid.
so the first thing i could do is to check if there is a pending order like this:
select count(id) into @cnt from tblorders where tuid = 1 and tcontid=5 and tstatus like 'pending';

and if it is > 0 then can't insert.
or i can just make a unique index for the three columns and that way the table won't accept new records of the duplicates.
the question is:
WHICH WAY IS FASTER? because thats gonna be a large database...

A:

Few suggestions.
use tstatus = 'pending'; instead of tstatus like 'pending';
Creating composite primary keys for tid, tcontid, tstatus may not work if you are considering only for 'pending' status.  What about other statuses?
If you decide to index the columns, I would recommend you create  a separate table for tstatus and use the foreign key reference here.  So it will save the space for the indexed columns and also your query will always run on the indexed fields.

