Hi,
now I've got a question about table and/or query design. It's about
having persons and contacts. Since every person should have as many
contacs as neccessary, I created two separate tables:
CREATE TABLE person (
pers_id serial PRIMARY KEY,
name character varying(50)
);
CREATE TABLE contact (
id serial PRIMARY KEY,
kind character varying(20),
type small int,
value character varying(75),
pers_id integer
);
where kind describes the kind of the contact: mobile, email, www, ...,
type says if this contact should be used as private or business contact
and whether or not this is the standard contact for this person for this
medium. value is the telephone number, email adress or whatever, pers_id
is a foreign key to the person table.
Now I'd like to have a query which includes some fields of person
combined with the standard business contact for mobile and email, where
it might happen that some persons do not have such a contact, but they
should be included in this list anyway.
I tried it with the following:
SELECT DISTINCT name, email, mobile
FROM person AS p
LEFT OUTER JOIN
(
SELECT person.pers_id, value as email
FROM person
INNER JOIN contact ON person.pers_id=contact.pers_id
WHERE kind='email' AND type=1
) AS q1 ON p.pers_id=q1.pers_id
LEFT OUTER JOIN
(
SELECT person.pers_id, value as mobile
FROM person
INNER JOIN contact ON person.pers_id=contact.pers_id
WHERE kind='mobile' AND type=1
) AS q2 ON p.pers_id=q2.pers_id;
type=1 means standard business contact
This gives exacly the resulted I'd like to have, but it is slow. I've
about 45000 entries in person and 44000 entries in contact and this
query takes 12 seconds, and if I add more joins for getting website,
telephone of course it's getting much worse. There are indexes on
person(pers_id), contact(id), contact(pers_id) and one on contact(kind,
type).
Thanks for reading all this stuff, and now my question: but could I do
to get the same result in less time? Changing the table design, changing
the query? I'm happy about any hint.
Regards,
Verena