6
\$\begingroup\$
SELECT p.name,m.name
FROM parts p
INNER JOIN Manufacturers m ON p.man_id=m.id
ORDER BY p.name DESC

If not, what should be done for its optimization?

Here is the two tables:

CREATE TABLE `Manufacturers` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(30) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB

CREATE TABLE `parts` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(30) NOT NULL,
    `man_id` int(11) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB
\$\endgroup\$
3
  • 3
    \$\begingroup\$ Looks fine to me. Just make sure you have indexes on the parts.man_id and Manufacturers.id fields. \$\endgroup\$
    – gen_Eric
    Commented Jun 13, 2012 at 15:36
  • \$\begingroup\$ it's hard to answer this without seeing the table-definitions and defined indexes/keys for both tables involved. \$\endgroup\$
    – oezi
    Commented Jun 13, 2012 at 15:39
  • \$\begingroup\$ See two tables below: CREATE TABLE Manufacturers ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(30) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB CREATE TABLE parts ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(30) NOT NULL, man_id int(11) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB \$\endgroup\$
    – user1454101
    Commented Jun 13, 2012 at 15:42

2 Answers 2

4
\$\begingroup\$
CREATE TABLE Manufacturers ( id int(11) NOT NULL AUTO_INCREMENT, 
name varchar(30) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB 

CREATE TABLE parts ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(30) NOT NULL, 
man_id int(11) NOT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB

your structure looks OK for performance, because you have index the common field where you make the join on. How ever, shouldn't you use PK and FK instead?. If you need to know a bigger description use the explain command to be sure that the index are correctly used.

\$\endgroup\$
1
  • 1
    \$\begingroup\$ +1. However, I think the "shouldn't you use a PK and FK instead?" Should read, "you should be using a foreign key." :) \$\endgroup\$
    – Corbin
    Commented Jun 14, 2012 at 19:59
1
\$\begingroup\$

The query is fine.

You should consider adding an index on parts.man_id.

The only thing I'd suggest is a small style improvement:
Whether you prefer left aligned or right aligned keywords doesn't matter much. But since joins are part of the FROM clause, I suggest you don't code that at the same indentation level.

SELECT  p.name,m.name
FROM    parts p
        INNER JOIN Manufacturers m ON
            p.man_id=m.id
ORDER BY p.name DESC
\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.