Help in understanding a simple query result
-
I am an SQL newbee & not understanding the results I am getting from a seemingly simple query. The query itself is returning the correct result but taking far too long. So I ran Explain to see what was happening. Sure enough more data is being examined than expected. But I can't figure out why. I have tried reordering the joins, but get the same result. Perhaps someone could educate me why it is returning so much data. Note: All ID's are integer types. The ID of the respective tables are the primary key and the ID's used in the Assemdata table are indexed. MySQL ver 5.x select assemdata.AssemNum innerpack.Descrip hanger.Descrip from hanger join innerpack join assemdata where assemdata.Assemnum >=60000 and assemdata.Assemnum <=60050 and assemdata.InnerPackTypeID = innerpack.InnerPackID and assemdata.hangerID = hanger.HangerID Explain Result: ID select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE innerpack ALL PRIMARY (Null) (Null) (Null) 3 Using where 1 SIMPLE assemdata ALL (Null) (Null) (Null) (Null) 2798 Using where 1 SIMPLE hanger ALL PRIMARY (Null) (Null) (Null) 2 Using where Query Result: AssemNum Descrip Descrip 60001 None Sawtooth 60002 None Sawtooth 60003 None Sawtooth ...... 49 Rows of data. Thanks for any suggestions!
-
I am an SQL newbee & not understanding the results I am getting from a seemingly simple query. The query itself is returning the correct result but taking far too long. So I ran Explain to see what was happening. Sure enough more data is being examined than expected. But I can't figure out why. I have tried reordering the joins, but get the same result. Perhaps someone could educate me why it is returning so much data. Note: All ID's are integer types. The ID of the respective tables are the primary key and the ID's used in the Assemdata table are indexed. MySQL ver 5.x select assemdata.AssemNum innerpack.Descrip hanger.Descrip from hanger join innerpack join assemdata where assemdata.Assemnum >=60000 and assemdata.Assemnum <=60050 and assemdata.InnerPackTypeID = innerpack.InnerPackID and assemdata.hangerID = hanger.HangerID Explain Result: ID select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE innerpack ALL PRIMARY (Null) (Null) (Null) 3 Using where 1 SIMPLE assemdata ALL (Null) (Null) (Null) (Null) 2798 Using where 1 SIMPLE hanger ALL PRIMARY (Null) (Null) (Null) 2 Using where Query Result: AssemNum Descrip Descrip 60001 None Sawtooth 60002 None Sawtooth 60003 None Sawtooth ...... 49 Rows of data. Thanks for any suggestions!
-
I have tried specifying the joins as "inner" - it made no difference. (My understanding is that MySQL interperts a join as "inner" unless otherwise specified.) I have also tried being specific on the join member i.e. join on hangerID. Also made no difference. Thanks!
-
I have tried specifying the joins as "inner" - it made no difference. (My understanding is that MySQL interperts a join as "inner" unless otherwise specified.) I have also tried being specific on the join member i.e. join on hangerID. Also made no difference. Thanks!
-
Yes - I have tried all combinations I could think of. The Explain output is identical no matter what order I use. Frustrating! Thanks
Hello Mr. Member 4723455, I always think it's better to put relational condition in "ON" clause of JOIN. You can try: select assemdata.AssemNum,innerpack.Descrip,hanger.Descrip from hanger join assemdata on assemdata.hangerID = hanger.HangerID join innerpack on assemdata.InnerPackTypeID = innerpack.InnerPackID where assemdata.Assemnum >=60000 and assemdata.Assemnum <=60050 Bye, (sorry for my bad English) :^)
-
Hello Mr. Member 4723455, I always think it's better to put relational condition in "ON" clause of JOIN. You can try: select assemdata.AssemNum,innerpack.Descrip,hanger.Descrip from hanger join assemdata on assemdata.hangerID = hanger.HangerID join innerpack on assemdata.InnerPackTypeID = innerpack.InnerPackID where assemdata.Assemnum >=60000 and assemdata.Assemnum <=60050 Bye, (sorry for my bad English) :^)
Suppaman, I have tried it both ways i.e. using "join on table1.id=table2.id" & using "where table1.id=table2.id". The results from an Explain Select are identical. But I agree the on method is easier to read. Thanks!
-
Suppaman, I have tried it both ways i.e. using "join on table1.id=table2.id" & using "where table1.id=table2.id". The results from an Explain Select are identical. But I agree the on method is easier to read. Thanks!
You can try using views: first create a view to filter out elements of assemdata with Assemnum >=60000 and Assemnum <=60050 (50 elems) then join this view with the other two table. In this way the query plan should be different and the query execution faster (I think). Bye ^__^