On Jun 11, 2005, at 2:03 PM, D. Richard Hipp wrote:
On Sat, 2005-06-11 at 12:32 -0500, Puneet Kishor wrote:
tbl1(a INTEGER PRIMARY KEY, b VARCHAR(200) UNIQUE) with 200k+ records
tbl2(a INTEGER PRIMARY KEY, b VARCHAR(200) UNIQUE) with a few k
records
SELECT *
FROM ((SELECT * FROM tbl1) UNION (SELECT * FROM tbl2))
WHERE b = 'foo'
or
SELECT * FROM tbl1 WHERE b = 'foo'
UNION
SELECT * FROM tbl1 WHERE b = 'foo'
The second form will be faster because of the index
on column b. (Whenever you use the UNIQUE keyword in
a column declaration, an index is created on that column
automatically.)
Fair enough. A couple of questions follow --
If I do have UNIQUE in a col declaration, will creating another index
on that column slow things down? Before I knew what you write above, I
had gone ahead and create a UNIQUE INDEX on col b. I am not sure if
that was slowing the operations, so I just want to confirm that.
Second, it seems logical that if I have UNIQUE then I don't have to
declare NOT NULL. For example,
b VARCHAR(200) UNIQUE NOT NULL
would be redundant.
If I have declared UNIQUE NOT NULL, should I go back and ALTER the
table to just UNIQUE so as to not experience and perf hits?
--
Puneet Kishor
