MySQL left join + where cause perform very slowly

where right we found that, when we do query using left join + where cause, the query is slower than just the query doing left join.

the left join + where cause should be able to use index, right?

Under what situtation can thsi kind of query perform slowly.

DBA100.
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
 
Mark WillsConnect With a Mentor Topic AdvisorCommented:
Not necessarily true that the "where" will always make it quicker.

While we might hope that to be always true, the optimizer looks at all the predicates and then decides what will be the best approach.

Now the optimizer heavily relies on statistics, so, if you really believe that the index should be used then maybe update the stats by using the "ANALYZE TABLE" command (there will be a reference in the MySQL doco).

Normally the "where" can help identify an appropriate index, except if there are conversions / functions and such like involved - the query becomes un-sargable. If a where clause might suggest an alternate index as maybe suggested by the optional join, then it can result in a sub-optimal plan.

It also depends on what column(s) your WHERE clause has been applied. If to the joined table, then the optimizer might decide that the best result is a scan.

To examine those type os issues, you really do need the EXPLAIN as angelIII has said. We could probably provide some more directed discussion if we also could see the query as yodercm has requested.

In MySQL you can control the optimizer a bit by setting search depth so it doesnt spend a whole lot of time wondering what plan might be best. Typically it should be set to zero so it decides itself, but if your queries never really exceed half a dozen table joins, then you can limit it a little more. But that is getting into slightly more advanced tuning...

Have a read of : http://dev.mysql.com/doc/refman/5.0/en/controlling-optimizer.html

And also note the next section 8.5 (navigation on the right hand side) to do with indexes.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
please post the EXPLAIN for both queries, it should help to understand
http://dev.mysql.com/doc/refman/5.0/en/explain.html
0
 
Cornelia YoderArtistCommented:
Do you really need the left join?  There are simpler ways that are faster.

If you post your query and table structure, there might be an alternative way to query.
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
marrowyungSenior Technical architecture (Data)Author Commented:
The query is :

"SELECT node.Region, node.CountryName, node.CityName,
                        node.ID node_id, node.Parent node_parent, node.InventoryCode node_inventoryCode,
                        node.InvStatusID node_status, node.Make node_make, node.Model node_model, node.Nomenclature,
                        slot.InventoryCode slot_inventoryCode, slot.InvStatusID slot_status, '   ' AS slot_usage, slot.Nomenclature,
                        card.ID card_id, card.Parent card_parent, card.InventoryCode card_inventoryCode, card.Model card_model,
                        card.InvStatusID card_status
                FROM mas_rpt_isd_node node
                        LEFT JOIN mas_rpt_isd_slot slot ON node.ID = slot.Parent
                        LEFT JOIN mas_rpt_isd_card card ON slot.ID = card.Parent
                WHERE node.Nomenclature='SDH' AND slot.Nomenclature='SDH'
"

after adding where cause it is much slower !
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
after explaining it. see attached .
MySQLquery.jpg
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
mark_wills, thanks for answering my question and  I know I post a lot recently because of 3 x new project need to be finish this month.  Please no rush on that and enjoy your life .
0
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
do you have a index on node.Nomenclature ?
if yes, you may need to increase the key_buffer_size parameter on mysql (+restart)

> AND slot.Nomenclature='SDH'
note that this will your LEFT JOIN implicitly a INNER JOIN.
the correct statement would be:
SELECT node.Region, node.CountryName, node.CityName,
                        node.ID node_id, node.Parent node_parent, node.InventoryCode node_inventoryCode,
                        node.InvStatusID node_status, node.Make node_make, node.Model node_model, node.Nomenclature,
                        slot.InventoryCode slot_inventoryCode, slot.InvStatusID slot_status, '   ' AS slot_usage, slot.Nomenclature,
                        card.ID card_id, card.Parent card_parent, card.InventoryCode card_inventoryCode, card.Model card_model,
                        card.InvStatusID card_status
                FROM mas_rpt_isd_node node
                        LEFT JOIN mas_rpt_isd_slot slot ON node.ID = slot.Parent
                        LEFT JOIN mas_rpt_isd_card card ON slot.ID = card.Parent AND slot.Nomenclature='SDH'
                WHERE node.Nomenclature='SDH'  

Open in new window


or to make it a inner join anyhow
0
 
Mark WillsTopic AdvisorCommented:
Agree with angelIII, except would have written (think he meant the "and...sdh" to go on the line above) :

SELECT node.Region, node.CountryName, node.CityName, 
       node.ID node_id, node.Parent node_parent, node.InventoryCode node_inventoryCode, 
       node.InvStatusID node_status, node.Make node_make, node.Model node_model, node.Nomenclature,
       slot.InventoryCode slot_inventoryCode, slot.InvStatusID slot_status, '   ' AS slot_usage, slot.Nomenclature,
       card.ID card_id, card.Parent card_parent, card.InventoryCode card_inventoryCode, card.Model card_model,
       card.InvStatusID card_status
FROM mas_rpt_isd_node node 
LEFT JOIN mas_rpt_isd_slot slot ON node.ID = slot.Parent AND node.Nomenclature = slot.Nomenclature
LEFT JOIN mas_rpt_isd_card card ON slot.ID = card.Parent 
WHERE node.Nomenclature='SDH'

Open in new window


That pretty much implies (a want for) an index using Parent + Nomenclature for mas_rpt_isd_slot

Including in the WHERE clause for a left join (ie rows are optional) not only implies an inner join, but also adds a predicate in addition to the "ON" which probably means a full scan.

Using the column names in the join is more optimizer friendly for the ON predicate rather than the literal (which could mean conversion from char to varchar etc). MySQL is pretty good (some will say brilliant), but the optimizer does do some interesting things we dont always expect (in comparison to say SQL Server).

P.S. And that comment you refer to was made on a different question :)
0
 
Ray PaseurCommented:
Is there any reason to consider using ORDER BY or LIMIT here?  Or do you want a complete table scan?
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
0
 
marrowyungSenior Technical architecture (Data)Author Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.