Optimize SQL with subquery in

Question

I would like to optimize the SQL statement, below is the original one.

SELECT DISTINCT p.productID, p.filename, p.model, p.code, p.manufacturerID, f2.manufacturerName, m.manufacturerName, CONCAT('INSERT INTO distribItems(productID, manufacturer, code, distributorText) VALUES (', CAST(p.productID AS CHAR), ', \'', f2.manufacturerName, '\', \'', f2.code, '\', \'', f2.denumire, '\') ;') INS FROM (SELECT f.manufacturerName, f.categoryName, f.code, f.denumire, f.code_2 FROM furnizorlist f LEFT JOIN distribitems d ON (d.manufacturer = f.manufacturerName AND (d.code = f.code OR d.manufacturer LIKE 'DELL') AND d.distributorText = LEFT(f.denumire, 450)) WHERE productID IS NULL) f2, products p, manufacturers m WHERE f2.code_2 <> '' AND (f2.code_2 = p.code_2 OR f2.code_2 = p.model_2) AND p.manufacturerID = m.manufacturerID AND m.manufacturerName = f2.manufacturerName AND m.manufacturerName != 'Compatibil' AND p.code != '1' ORDER by p.filename ASC; 

On my PC, it takes about 34 seconds.

My idea was to write a subquery as Join and set conditions in the Where clause.

Here is my incredible fast SQL:

 SELECT DISTINCT p.productID, p.filename, p.model, p.code, p.manufacturerID, f.manufacturerName, m.manufacturerName, CONCAT('INSERT INTO distribItems(productID, manufacturer, code, distributorText) VALUES (', CAST(p.productID AS CHAR), ', \'', f.manufacturerName, '\', \'', f.code, '\', \'', f.denumire, '\') ;') INS FROM furnizorlist f, distribitems d, #subquery end products p, manufacturers m WHERE d.manufacturer = f.manufacturerName AND (d.code = f.code OR d.manufacturer LIKE 'DELL') AND d.distributorText = LEFT(f.denumire, 450) AND d.productID IS NULL #subquery condions end (f and d tables) # the next is a subquery result: AND f.code_2 <> '' AND (f.code_2 = p.code_2 OR f.code_2 = p.model_2) AND p.manufacturerID = m.manufacturerID AND m.manufacturerName = f.manufacturerName AND m.manufacturerName != 'Compatibil' AND p.code != '1' ORDER by p.filename ASC; 

If I write explain improved_sql , I will see the Impossible WHERE column. I tried, but could not understand why this is impossible. I checked the compatibility of the fields: there is no case when you want to compare int with varchar and the like. I could not find a serious mistake, so I am here.

Is there a logical error in the WHERE clause? for example, request field 1 and then 2 ?

Fields ending with identifier are INT Fields ending with NAME are varchar (255) Fields with text are 450 and 8192 varchar (only in one place)

traslation: "denumire" means "description" - or something like that :)

Not sure which version of mysql is running on the server side, maybe 5.

I can suggest changes to the structure of the table, if I have a good reason, perhaps the code will be adjusted.

EDIT:

The impossible where it occurs:

 EXPLAIN SELECT f.manufacturerName, f.categoryName, f.code, f.denumire, f.code_2 FROM furnizorlist f INNER JOIN distribitems d ON (d.manufacturer = f.manufacturerName AND (d.code = f.code OR d.manufacturer LIKE 'DELL') AND d.distributorText = LEFT(f.denumire, 450)) WHERE productID IS NULL 

Note: INNER JOIN, not LEFT JOIN.

Edit2: Table: furnizorlist 42,751 records Table: distributions 72,290 records

Answer 1

Not quite sure, but here are a few points that many help if you intend to use an internal table (you are the first example) - you really need to try to narrow down what it repels. This may mean writing some redundant sentences. For example, “where order_dt is between 2001 and 2002” may appear in a smaller internal table and main query. What for? Since in some situations the internal table cannot be optimized, the result is a million rows that the server retrieves temporarily, for the few that you need.

Also, I noticed a line function in one of your connections: "LEFT (f.denumire, 450))"

During the connection, any function should be avoided, which forces the server (one after the other) to evaluate each individual record .. it cannot optimize. This is somewhat similar to why you should always use primary keys to connect, but more time. Better stick to "like" = AND, NOT, OR, IN .. etc.