CODEWITHSUNDEEP
mysql
medo ampir
medo ampir

Reputation: 1920

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...

Upvotes: 0

Views: 49

Answers (3)

Gardner Bickford
Gardner Bickford

Reputation: 1963

Assuming that a user will attempt to add a conflicting record less often than a valid record then the compound index will be faster. The SQL engine will maintain the index and throw an error when there is an index constraint violation.

Even if you did elect the select method you would need to maintain the index. Aside from that, pulling a result set from a select all the way back into your application's memory space and then checking the result is much slower than enforcing it on an index constraint.

For more info please see: https://dev.mysql.com/doc/refman/5.5/en/multiple-column-indexes.html

Upvotes: 1

Slowcoder
Slowcoder

Reputation: 2120

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.

Upvotes: 1

artragis
artragis

Reputation: 3713

An index is clearly faster as it is designed for that usecase. It will fasten the search of the tuple you are looking for and if the constraint is not satisfied, it will send an error so, in your treatment script you will handle it easier (and faster) than by fetching the result, and so.

Upvotes: 1

Related Questions