Optimize a JOIN query, for multiple correspondances between tables

Question

I am trying to optimize a function (MySQL), but the JOIN is still not completely understood (I try to make a simple example) :

I have 2 tables :

Table ITEMS

ID  |  ID_ORDER | ID_BOX | NAME

001 |  2564     | 123    | SPOON_1
002 |  2564     | 123    | SPOON_2
006 |  2564     | 123    | SHOES_2
007 |  2564     | 123    | SHOES_1
003 |  2565     | 124    | SPOON_1
004 |  2565     | 124    | SPOON_2
008 |  2565     | 124    | SHOES_1
009 |  2565     | 124    | SHOES_2
005 |  2565     | 125    | SPOON_1
010 |  2565     | 125    | SHOES_1

The description of objects are linked to the ITEM table by ID_CONTRACT, and NAME.(Not possible to have 2 items with same name inside the same contract).

Table DESCRIPTION :

ID_ORDER | NAME_ITEM | LENGTH | WIDTH | ....

2564     | SPOON_1   | 200    | 20 ...
2564     | SPOON_2   | 300    | 20 ...
2564     | SHOES_1   | 500    | 20 ...
2564     | SHOES_2   | 600    | 20 ...

Now, I need to know all items I have in the contract, with their description, and I use this query :

SELECT *,description.* FROM items INNER JOIN description 
ON (description.ID_CONTRACT=items.ID_CONTRACT AND description.NAME_ITEM=items.NAME) 
WHERE ID_CONTRACT= 2564

First, I just read it is not correct query (I need to copy all description fields by hand in query?), because ID_CONTRACT is in both tables, and sometimes it gives me mistake(sometimes not), and I read there that it is not possible to ignore duplicates. Then I am wondering, as I make a select on ITEMS table, MySQL is looking for each line a correspondance in DESCRIPTION table? Is there a way to optimize query (another kind of JOIN), so it will not search everytime in ITEMS table, when he meets 2 elements (or more) in ITEMS, with same ID_CONTRACT/NAME ?

Answer 1

select * mean select all the columns from all the tables and in your case this is like select items.,description. so with the syntax SELECT ,description. seems you are trying to select two time the columns for table description

due the fact you have the same column name ID_CONTRACT in both table this produce an ambiguity on coumn name for the DB engine during the join. for avoid this you simply need a full reference name in join columns name eg:

table1.col1 = table2.col1 

this way the db engino know which column form each table must be use for join

SELECT items.*, description.* 
FROM items 
INNER JOIN description ON description.ID_CONTRACT=items.ID_CONTRACT 
    AND description.NAME_ITEM=items.NAME
WHERE ID_CONTRACT= 2564

for your second part of question

 MySQL is looking for each line a correspondance in DESCRIPTION table?

yes.

A relation db work on sets of data and retrieve all the correspondance between the tables

if really the rows are duplicated you could retrive the distinct result using

 select DISTICNT col1, col2..

tipically a select * from a join on correctly normalized data set don't produce dulicated rows (at least you value in one column differ beetween the rows)

but if some column result are not important for you and can be omittedc form the result, this case can produce a result with duplicated row and you can perform a selective select using only the column name you really need and apply the disctint clause