SQL Help

[TheBuzzer]

Hi I am trying to join a queue where the part that is faking forever is when I am trying to make a style number match to the style description in a table of items. for example


order_items:
12-123
12-123
43-153
12-542
12-123
31-432


styledesctable
12-123 = "small fish"
12-542 = "small cat"
43-153 = "large fish"

and make them become into a single result of

item desc
12-123 "small fish"
12-123 "small fish"
43-153 "large fish"
12-542 "small cat"
31-432 NULL


I tried using left join but that makes the sql query extremely slow
Inner join does not have any null results.

Is there a good way to join the tables?

 

[calebb]

How many results are returned when you execute the left join?

If performance is lower than you need, you could be returning more rows than you actually need or you might be missing an index.

Which SQL server (MSSQL, MySQL, PosteGRE, etc) & what version are you using?

 

[TheBuzzer]

I am using mysql

I got a feeling it is because of a missing index because I was merging tables

SELECT newtable.eclipseaccountnumber,newtable.contactname,newtable.stylenumber,newtable.totalquantity,desctable.description FROM (SELECT ordermain.eclipseaccountnumber,ordermain.contactname,ordermain_sets_items.stylenumber, SUM(ordermain_sets_items.quantity) AS totalquantity
FROM ordermain_sets_items
INNER JOIN ordermain ON ordermain_sets_items.hiddenkey = ordermain.hiddenkey
INNER JOIN ordermain_sets ON ( ordermain_sets_items.hiddenkey = ordermain_sets.hiddenkey
AND ordermain_sets_items.set = ordermain_sets.set )
WHERE ordermain.orderstatus = 'Order Completed'
AND ordermain_sets_items.set < ordermain.setcount
AND ordermain_sets_items.item < ordermain_sets.itemcount
AND ordermain_sets_items.quantity IS NOT NULL
AND ordermain_sets_items.stylenumber != '' GROUP BY ordermain_sets_items.stylenumber,ordermain.eclipseaccountnumber) AS newtable LEFT JOIN
(SELECT sku AS stylenumber, productname AS description
FROM `styles_domestic`
UNION SELECT sku AS stylenumber, productname AS description
FROM `styles_domesticshirts`
UNION SELECT Style AS stylenumber, Name AS description
FROM `styles_lackpard`
UNION SELECT Style AS stylenumber, `Emb Tooling Name` AS description
FROM `styles_overseas`
UNION SELECT Style AS stylenumber, CONCAT( `Style of Cap (Low Profile Pro Style, Pro Style)`  , ' ', `Material Description`  ) AS description
FROM `styles_pre-designed`) AS desctable ON newtable.stylenumber = desctable.stylenumber[

the result is like 10,000 tables.

as you can see the description for the style is really made from lot of different tables.
That part is fast. the slow part is the left join part.

not sure if I can put a index on the temp table i made.

 

[Crax]

the result is like 10,000 tables.

10,000 rows you mean?

Do you have primary/foreign keys defined? Are you using myISAM vs innoDB?

 

[TheBuzzer]

i am using myISAM. i really have no clue whats the different engines so I just kept it as the default.

ya 10,000 rows i mean.

I have a primary key defined for all the tables i have but when i joined the table to make the desctable. I dont know if I can define a primary key for that.

 

[TheBuzzer]

seems like what I am doing is called derived table and it seems like there is no way to add a index to that?

 

[Sgraffite]

You're left joining on a temp table created from a union of five sub queries. You can't add indexes to temp tables as they only exist for the duration of the query. You also have all of that within a sub query.

So you have nested sub queries that contain unions, which is probably the majority of your performance problems. I'd shoot for doing the query with no sub queries if possible.

Also it appears you are using strings as your primary indexes since they have hyphens in them? I'd recommend using int data types for that.

 

[TheBuzzer]

na the data structure can't be changed.

What i need to do I think is figure out how to insert all the style desc into a temp table with a index. I was testing all my sql commands using phpmyadmin but it seems like when i try to create temp table in php myadmin i get a sql error.

 

[TheBuzzer]

Ok i figure out how to fix the slowness. CREATE TEMPORARY TABLE desctable with unique style number.

than joined everything together.

now there is barely any lag.