Has the following query been made in the optimum way?

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

Answers


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.


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

Need Your Help

Grails prevent duplicate inserts

grails groovy gorm

how do i prevent duplicate inserts of a domain class?

How make VBA run on clicking any checkbox in a userform?

vba excel-vba excel

I have a userform with a multiple frames, all filled with multiple checkboxes. I've named the checkboxes to their corresponding Excel cells. Now I want to make VBA run on clicking any of these chec...