Has the following query been made in the optimum way?

Question

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

Answer 1

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.

Answer 2

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