Optimize a JOIN query, which "JOIN" would be the best?

Question

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

Table DESCRIPTION :

ID_ORDER	NAME	LENGTH	WIDTH	....
2564	SPOON_1	200	20 ...
2564	SPOON_2	300	20 ...
2564	SHOES_1	500	20 ...
2564	SHOES_2	600	20 ...

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

The way I found faster is using C# (I compared all times) :

1. I make a request SELECT * FROM items WHERE ID_ORDER=2567, that returns me a List<Item>.

2. Then I group by name, to have the list of items'names in the list :

   List listNames=listItems.Select(x=>"'"+x.Name+"'").Distinct().ToList();

3. I make a second request that will look for all descriptions corresponding :

   string query = "SELECT * FROM description WHERE ID_ORDER=2567 AND NAME IN ("+string.Join(",",listNames)+")";

Even if I have 1000 items in my order, but only 50 different items, in 1st query I return all 1000 items, then on 2nd query I return only 50 descriptions.

So on the end I just need to do something like :

foreach(Item item in listItems)
{
    item.Description=listDescriptions.FirstOrDefault(x=>x.Name==item.Name);
}

Now, I am trying to do all in a single SQL request. Here is what I have now :

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

The problem, as I understand, is SQL will search the list of items, then will search a description for each of them, and it looks more time, because the description is big data. Is there a faster way that would make SQL first read all descriptions corresponding to my list of items, then apply description to each of them? I also tried RIGHT JOIN (I believed it would act like that), but it take almost as much time as simple JOIN, using C# is still the faster way.

Answer 1

You can use the EXPLAIN command, that will let you know how MySQL is planning to answer your query. You can use it to optimise your query to run faster, or just to understand how it works. See https://dev.mysql.com/doc/refman/8.0/en/explain.html

For example:

EXPLAIN SELECT * FROM items INNER JOIN description 
ON (description.ID_ORDER=items.ID_ORDER AND description.NAME=items.NAME) 
WHERE ID_ORDER=2564

Answer 2

JOIN includes the rows both tables that match based on the ON. (Or the relationship may be hiding in the WHERE, where they don't belong. WHERE should be used only for filtering.)

LEFT JOIN includes all the rows of the "left" table and any matching rows of the "right" table, whether or not they match. Non-matching rows show NULL in the columns.

FROM a LEFT JOIN b ON ... WHERE b.id IS NULL is the pattern for finding rows of a for which the corresponding b row is missing`.

RIGHT JOIN is a LEFT JOIN with the tables swapped. (Don't use this; it makes my brain hurt.)

CROSS JOIN has all combinations of the rows of both tables. It is equivalent to having no ON and no 'relationship' in the WHERE clause.

"commajoin" is old syntax: FROM a,b WHERE a.foo=b.foo. Don't use it; responders on this forum will slap your hand if you do.

The keywords INNER and OUTER are irrelevant and ignored by MySQL.

FULL OUTER JOIN is a feature and syntax that MySQL does not support. It can be simulated with a JOIN and a LEFT JOIN combined by a UNION.