Question about foreign key
Dear sir,
Hi, I am Terence and using Postgresql 7.0 in linux
server and the web server can connect the database.
I want to ask one question:
I have created two tables
1.
CREATE TABLE sys_user (
id serial primary key,
login_name varchar(32),
password varchar(32)
)
2.
CREATE TABLE sys_quest_list (
id serial primary key,
owner_id int4,
quest_name varchar(100)
)
I add a foreign to sys_quest_list
ALTER TABLE sys_quest_list ADD CONSTRAINT sqloifk
foreign key(owner_id) REFERENCES sys_user(id);
The sys_quest_list.owner_id is the foreign key to
sys_user.id
When I do the select SQL like this
"select login_name,quest_name FROM sys_quest_list
JOIN sys_user ON owner_id=sys_user.id"
Can the added foreign key improve the above sql
performance so that it can search faster
or the foreign key can only maintain the data
integrity but cannot make the search faster?
Your help would be much appreciated.
Looking forward to your reply.
Thank you very much
=====
Yours sincerely,
Leung Chun Man, Terence
Mobile: (852) 9273 9176
Homepage: http://tcmleung.uhome.net ,
http://www.dynamicdrive.com/dynamicindex4/filter/index.htm
__________________________________________________
Do you Yahoo!?
HotJobs - Search new jobs daily now
http://hotjobs.yahoo.com/
Date: Wed, 6 Nov 2002 03:49:49 -0800 (PST)
From: Terence Leung <tcmleung@yahoo.com>Hi, I am Terence and using Postgresql 7.0 in linux
You should consider an upgrade to 7.2 or (if you can wait some months)
to 7.3. These versions are *strong* improvements.
"select login_name,quest_name FROM sys_quest_list
JOIN sys_user ON owner_id=sys_user.id"Can the added foreign key improve the above sql
performance so that it can search faster
No.
or the foreign key can only maintain the data
integrity but cannot make the search faster?
Yes.
For better search performance, do a CREATE INDEX and don't forget to
ANALYZE the table.
Christoph Dalitz
Import Notes
Reply to msg id not found: 20021107140430.D4C5047702F@postgresql.orgReference msg id not found: 20021107140430.D4C5047702F@postgresql.org | Resolved by subject fallback
On Wed, Nov 06, 2002 at 03:49:49AM -0800, Terence Leung wrote:
Dear sir,
Hi, I am Terence and using Postgresql 7.0 in linux
server and the web server can connect the database.
You'll be wanting to upgrade that. 7.0 is old and has had several
bugs fixed.
Can the added foreign key improve the above sql
performance so that it can search faster
or the foreign key can only maintain the data
integrity but cannot make the search faster?
It is only an integrity contraint. It makes no difference to speed
(well, that's not true. It slows some other things down).
Is you are having a performance problem, all the usual questions
(vacuums, &c) apply. But do upgrade first. Among other things,
performance was much improved in 7.2.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110