SQL or table design optimation
От | Verena Ruff |
---|---|
Тема | SQL or table design optimation |
Дата | |
Msg-id | 447C6194.3020404@triosolutions.at обсуждение исходный текст |
Ответы |
Re: SQL or table design optimation
Re: SQL or table design optimation |
Список | pgsql-novice |
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
В списке pgsql-novice по дате отправления: