Re: Optimization of this SQL sentence

On Oct 17, 2006, at 11:33 , Ruben Rubio wrote:

> CREATE TABLE "comment"
> (
> idcomment int4 NOT NULL DEFAULT
> nextval('comment_idcomment_seq'::regclass),
[snip 28 columns]
> CONSTRAINT comment_pkey PRIMARY KEY (idcomment)
> )
>
> Ficha structure:
> No indexes in ficha
> Ficha rows: 17.850
>
> CREATE TABLE ficha
> (
> idficha int4 NOT NULL DEFAULT nextval
> ('ficha_idficha_seq'::regclass),
[snip 67 (!) columns]
> CONSTRAINT pk_ficha PRIMARY KEY (idficha),
> CONSTRAINT fk_ficha_geonivel6 FOREIGN KEY (idlocacion) REFERENCES
> geonivel6 (idgeonivel6) ON UPDATE NO ACTION ON DELETE NO ACTION
> )

These tables are particularly egregious examples of ignorant
database design. You need to understand the relational model (http://
en.wikipedia.org/wiki/Relational_model) specifically data
normalization (http://en.wikipedia.org/wiki/Database_normalization)
and 3NF (http://en.wikipedia.org/wiki/3NF)

These columns are particularly telling:

searchengine1 int4,
searchengine2 int4,
searchengine3 int4,
searchengine4 int4,
searchengine5 int4,
searchengine6 int4,
deseo1 int4,
deseo2 int4,
deseo3 int4,
deseo4 int4,
deseo5 int4,
deseo6 int4,
titulomapa_l0 varchar(255),
titulomapa_l1 varchar(255),
titulomapa_l2 varchar(255),
titulomapa_l3 varchar(255),
titulomapa_l4 varchar(255),
titulomapa_l5 varchar(255),
titulomapa_l6 varchar(255),
titulomapa_l7 varchar(255),
titulomapa_l8 varchar(255),
titulomapa_l9 varchar(255),

Refactor into three separate tables:

create table searchengine (
idficha int references ficha (idficha),
searchengine int,
primary key (idficha, searchengine)
);

create table deseo (
idficha int references ficha (idficha),
deseo int,
primary key (idficha, deseo)
);

create table titulomapa (
idficha int references ficha (idficha),
titulomapa int,
primary key (idficha, titulomapa)
);

Now you can find all search engines for a single ficha row:

select searchengine from searchengine where idficha = n

This design allows for more than 5 search engines per ficha row, and
allows expressive joins such as:

select ficha.idficha, searchengine.searchengine
inner join searchengine on searchengine.idfciha = ficha.idficha

Also, most of your columns are nullable. This alone shows that you
don't understand your own data.

Lastly, note that in PostgreSQL these length declarations are not
necessary:

contacto varchar(255),
fuente varchar(512),
prefijopais varchar(10)

Instead, use:

contacto text,
fuente text,
prefijopais text

See the PostgreSQL manual for an explanation of varchar vs. text.

Alexander.