Solved

MySQL Query for stock delivered and sold

Posted on 2016-08-31
11
50 Views
Last Modified: 2016-09-15
Hello Experts Exchange
I have a MySQL query that takes a long time to run and is not returning any data, but parts of the query run quickly when I run smaller sections.

This is my query that returns no data.
Select Z.zip,Sum(sl_order.sales_qty) as sales_qty
From sl_transaction 
inner join sl_order on sl_transaction.id = sl_order.transaction_id
			inner join (select dl2.id,stk_transfer.received_date,stk_transfer.stk_item_id, address.zip
			from stk_transfer
			inner join data_location dl2 on stk_transfer.del_loc_id = dl2.id
			inner join address on dl2.address_id = address.id
			Where stk_transfer.received_date between DATE_FORMAT('2016-05-01','%Y-%m-%d %h:%i:%s')  
			and DATE_FORMAT('2016-08-26','%Y-%m-%d %h:%i:%s') 			
                        and desp_loc_id = 101
			and del_loc_id <> 8) as Z
on Z.id = sl_transaction.location_id
and Z.received_date < sl_transaction.updated_at
and Z.stk_item_id = sl_order.stk_item_id
Where sl_transaction.updated_at > DATE_FORMAT('2016-05-01','%Y-%m-%d %h:%i:%s')
group by Z.zip

Open in new window


I think it is performing a full table scan of the sl_order table but I don't know how to change my query to stop that.

Can anyone suggested how I can change my query so it returns data?

Regards

SQLSearcher
0
Comment
Question by:SQLSearcher
  • 6
  • 4
11 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41777848
If you believe there is a full table scan, why not use an explain plan to get the details?

EXPLAIN
   your-query-goes-here
;
Can you provide that explain plan details here please?

Generic advice would be to have some index for EVERY column referenced by a JOIN and in the WHERE clause. You could also look at this article "3 Ways to Speed Up MySQL"


By the way, are you CERTAIN that you are not multiplying the result of your sum() by those joins? When you sum() over a set of joined tables getting a very inflated result is very common.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41777862
This MIGHT work as an alternative, but without access to your tables (or at least sample data from each table) it is quite a guess:
SELECT
      address.zip
    , SUM(sl_order.sales_qty) AS sales_qty
FROM stk_transfer
      INNER JOIN sl_order ON stk_transfer.stk_item_id = sl_order.stk_item_id
      INNER JOIN data_location dl2 ON stk_transfer.del_loc_id = dl2.id
      INNER JOIN address ON dl2.address_id = address.id
WHERE stk_transfer.received_date BETWEEN DATE_FORMAT('2016-05-01', '%Y-%m-%d %h:%i:%s')
      AND DATE_FORMAT('2016-08-26', '%Y-%m-%d %h:%i:%s')
      AND desp_loc_id = 101
      AND del_loc_id <> 8
      AND EXISTS (
            SELECT
                  NULL
            FROM sl_transaction
            WHERE sl_transaction.updated_at > DATE_FORMAT('2016-05-01', '%Y-%m-%d %h:%i:%s')
                  AND sl_transaction.location_id = address.zip
                  AND sl_transaction.id = sl_order.transaction_id
      )
GROUP BY
      address.zip

Open in new window

0
 

Author Comment

by:SQLSearcher
ID: 41777946
Hello Paul
I tried your query it did the same and ran for a long time with no data.

Here is the Explain Plan attached.

Regards

SQLSearcher
Explain-Query.csv
0
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 
LVL 48

Expert Comment

by:PortletPaul
ID: 41778873
That derived table is the source of slowness it is being written to disk and sorted (see the first row of data in that explain plan).  

can you provide the explain plan for the variant I provided?
and if possible can you supply that in the fixed width layout you see at command line?
0
 

Author Comment

by:SQLSearcher
ID: 41779449
Hi Paul
I had to change a line of your code, from this;
AND sl_transaction.location_id = address.zip
To this;
AND sl_transaction.location_id = dl2.id

Please find Explain plan attached, sorry could not save it fixed width.

Regards

SQLSearcher
Explain-Query2.csv
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41779541
Unable to interpret the csv via a phone im afeaid but it is still using a filesort.

Please check EVERY column used in a join.
Is there an index for that column?

If no then you need that index.

If all the joins are covered by indexes then check each column referenced in the where clause. Is eaxh of these covered by an index?

The explain plan, when seen as a table, should make it clear  what isn't using an index.

Sorry I'm travelling and only have a phone right now.
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 41779697
Do you have indexes on any of the date fields?

Both of these are used in where clauses:
     stk_transfer.received_date
     sl_transaction.updated_at

The are not showing as possible indexes in the explain plan and so suspect that is impacting on the performance.
0
 

Author Comment

by:SQLSearcher
ID: 41780106
Hi Paul
Both fields have indexes on them, could there be something else?

Regards

SQLSearcher
0
 
LVL 48

Accepted Solution

by:
PortletPaul earned 400 total points
ID: 41780711
My query is better than the first ... I don't see how it can be improved at this point (look for green)
Explain for queries 1 and 2there are also fewer rows in the second result.

Both queries still use disk and filesort however.
0
 
LVL 25

Assisted Solution

by:Tomas Helgi Johannsson
Tomas Helgi Johannsson earned 100 total points
ID: 41793944
Hi!

After looking at your query make sure you have these two indexes on your tables.
CREATE INDEX updat_ix on sl_transaction ( updated_at desc, location_id asc, id asc );
CREATE INDEX salesqty_ix on sl_order ( transaction_id asc, stk_item_id asc, sales_qty asc );

Regards,
     Tomas Helgi
0
 

Author Closing Comment

by:SQLSearcher
ID: 41799464
Hello
Thank you both for your help.

Regards

SQLSearcher
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

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

730 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