Solved

MySQL left join + where cause perform very slowly

Posted on 2013-02-05
16
481 Views
Last Modified: 2013-06-04
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.
0
Comment
Question by:marrowyung
  • 9
  • 2
  • 2
  • +2
16 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38854173
please post the EXPLAIN for both queries, it should help to understand
http://dev.mysql.com/doc/refman/5.0/en/explain.html
0
 
LVL 27

Expert Comment

by:yodercm
ID: 38854655
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
 
LVL 51

Accepted Solution

by:
Mark Wills earned 250 total points
ID: 38901602
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
 
LVL 1

Author Comment

by:marrowyung
ID: 38908040
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
 
LVL 1

Author Comment

by:marrowyung
ID: 38908043
after explaining it. see attached .
MySQLquery.jpg
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38908161
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
 
LVL 142

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 250 total points
ID: 38908367
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
 
LVL 51

Expert Comment

by:Mark Wills
ID: 38908508
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 108

Expert Comment

by:Ray Paseur
ID: 38922908
Is there any reason to consider using ORDER BY or LIMIT here?  Or do you want a complete table scan?
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 38941740
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38952554
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38952926
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38952927
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38952930
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38956923
0
 
LVL 1

Author Comment

by:marrowyung
ID: 38998617
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Suggested Solutions

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now