0

Let's take a really simple situation.

In a trigger, before any insertion, I put a rule which forbid an indexed key to be present twice or more ( the primary is constituted with more than one key).

Only working with indexed keys here.

if(
        ( select count( *)
        from Freaking_Huge_Table t1
        where t1.PK_Part2 = NEW.PK_Part2 ) > 0
) then
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = "wise message";
end if;

So the question goes like this, will it stop after finding the first occurrence or will it count them all ? (The rule doesn't apply to records set before the trigger was installed so it is possible)

Or for the following condition, will it directly try to match while searching or would it build the table entirely and then search through it afterwards?

NEW.PK_Part2 IN ( 
   select t1.PK_Part2
   from Freaking_Huge_Table t1)

Adding a "limit 1" clause would certainly solve the presented problem but I can imagine fancier situations where it wouldn't be possible.

Adrien Horgnies
  • 691
  • 4
  • 11
  • 1
    The fanciest solution is to put a unique index on the column! – e4c5 May 15 '16 at 04:20
  • If you can't use a `UNIQUE` index, then use an `EXISTS` test. The `SELECT COUNT(*)` will do the entire count; it will not short-circuit; `EXISTS` does. – Rick James May 25 '16 at 06:00

0 Answers0