2uzhan.com
Advertisement
Now Place:2uzhan.com » Too many left joins? - newbie stuff, help needed.

Too many left joins? - newbie stuff, help needed.

MySQL @ November 22, 2006   Views:0

Hi all.

I would like to get your opinion on how effective my query is, i am sure gurus and alike would see immediately if I am doing some newbie mistakes. I will try to explain my simple database.

basically a table of products exists, where each product has a name, price, qty, unit of qty measure, package, and distributor. The website which operates on this information is multi-lingual, so naturally all text has to have several versions - that includes product names, product package names, and product distributor names. A table for packages and one for distributors also exist.

Code:

create table multi_language_text
(
    id int not null,
    txt text character set utf8,
    language_id int not null,
    primary key (id, language_id)
);

create table product_makers
(
    id int not null,
    primary key (id),
    foreign key (id) references multi_language_text(id)
);

create table product_packages
(
    id int not null,
    primary key (id),
    foreign key (id) references multi_language_text(id)
) engine=InnoDB;

create table products
(
    id int not null,
    maker_id int not null,
    package_id int not null,
    qty real not null,
    unit char not null,
    price real not null,
    primary key (id, maker_id, package_id, qty, unit),
    foreign key (id) references multi_language_text(id),
    foreign key (package_id) references product_packages(id),
    foreign key (maker_id) references product_makers(id)

);

Now, to retrieve the list of all products, with names, package names, and distributor names instead of numbers, i use following query:

Code:

select
    p.id as id, pl.txt as name, pml.txt as maker, ppl.txt as package, p.qty as qty, p.unit as unit, p.price as price
from
    products as p
    left join
    multi_language_text as pl
    on p.id = pl.id
    left join
    multi_language_text as pml
    on p.maker_id = pml.id
    left join
    multi_language_text as ppl
    on p.package_id = ppl.id
where pl.language_id =1 and pml.language_id =1 and ppl.language_id =1;

Do you think this is over the top? I am fairly new to SQL (about 1-2 years of here-and-there experience making it maybe 3 months pure SQL time), but I did my reading, and i am also a programmer.

Should i replace the join thing with subqueries ? Also isn't it possible to eliminate text for other languages than the chosen (=1) more effectively, without doing it three times ?

The database is MySQL 4.1, so views are out of the questions..

Thanks.

--------------Solutions-------------

have you tried running those statements? because i think they will fail

this won't work --

Code:

foreign key (id) references multi_language_text(id)

since the multi_language_text table has a composite primary key, you cannot reference only part of it

It does (MySQL?), although when writing the query i was sure that the standard would not allow it.

Anyways, apart from the constraints, do you think the left join style query is any effective ?

the tables have to be InnoDB in order for the foreign keys to work

as far as your left join query is concerned, i cannot comment on it because i don't understand your table design

All tables are supposed to be InnoDB. I dont know how it slipped out only product_packages specifies the InnoDB engine... In my sql files all tables specify InnoDB engine.

The table design is (poorly?) explained in the topic.

Thanks for the help. For now I left the query as is, because the practical speed implications are negliagible (for now).

Tags:
© 2018 2uzhan.com Contact