Solved

MySQL left join + where cause perform very slowly

Posted on 2013-02-05
16
485 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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
 
LVL 109

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 109

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
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…

815 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

8 Experts available now in Live!

Get 1:1 Help Now