Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2009
    Location
    Waltham, Massachusetts, United States
    Posts
    18
    Plugin Contributions
    0

    Default Slow query in mysql

    We have a site at https://www.belmetric.com that we have been running successfully for over 10 years. It is currently running Zen 1.5.4, with PHP 5.6.29 and mysql 5.1.73-log.

    The site is pretty fast on the customer side, but the admin side - specifically the order list - is painfully slow. Getting a slow query report from MySQL shows some slow queries:

    This one I know is the order page load. It takes a long time to load (query is over 11 seconds) the first time the page is requested, and then is OK until another order posts - I assume this is because it is cached by mysql. It also loads 20 rows.

    # Time: 200427 9:56:50
    # [email protected]: xxxxxxxxx[xxxxxxxxxx] @ localhost []
    # Query_time: 11.104898 Lock_time: 0.000128 Rows_sent: 20 Rows_examined: 538580
    SET timestamp=1587999410;
    select o.orders_id, o.customers_id, o.customers_name, o.payment_method, o.shipping_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total, o.customers_company, o.customers_email_address, o.customers_street_address, o.delivery_company, o.delivery_name, o.delivery_street_address, o.billing_company, o.billing_name, o.billing_street_address, o.payment_module_code, o.shipping_module_code, o.ip_address from (orders_status s, orders o ) left join orders_total ot on (o.orders_id = ot.orders_id and ot.class = 'ot_total') where (o.orders_status = s.orders_status_id and s.language_id = '1') order by o.orders_id DESC limit 40, 20;


    This one is a mystery to me -

    # Time: 200427 9:57:58
    # [email protected]: xxxxxxxxx[xxxxxxxxxx] @ localhost []
    # Query_time: 13.385325 Lock_time: 0.000112 Rows_sent: 106778 Rows_examined: 645298
    SET timestamp=1587999478;
    select o.orders_id, o.customers_id, o.customers_name, o.payment_method, o.shipping_method, o.date_purchased, o.last_modified, o.currency, o.currency_value, s.orders_status_name, ot.text as order_total, o.customers_company, o.customers_email_address, o.customers_street_address, o.delivery_company, o.delivery_name, o.delivery_street_address, o.billing_company, o.billing_name, o.billing_street_address, o.payment_module_code, o.shipping_module_code, o.ip_address
    from (orders_status s, orders o )
    left join orders_total ot on (o.orders_id = ot.orders_id and ot.class = 'ot_total')
    where (o.orders_status = s.orders_status_id and s.language_id = '1') order by o.orders_id DESC;

    It looks like it sends 106778 rows, because it is not limited.

    Do we know what is looking for so many orders, and is there a way of optimizing this so it runs faster?

    TIA

  2. #2
    Join Date
    Jan 2004
    Posts
    65,400
    Blog Entries
    7
    Plugin Contributions
    232

    Default Re: Slow query in mysql

    When looking at this same issue on another very large site I observed 2 factors:

    1. Adding these indexes seemed to help in some cases:
    Code:
    ALTER TABLE orders ADD INDEX on_status_date_id (orders_status,date_purchased,orders_id);
    ALTER TABLE orders_total ADD INDEX on_ot_id_class (orders_id,class);
    2. I think the secondary issue is that the query's joins to other large tables (particularly orders_total) is what's making it slow. Not that the query is slow, but the pagination calculation with split_page_results (which re-runs the queries to determine pagination indicators) slows it down.
    .

    Zen Cart - putting the dream of business ownership within reach of anyone!
    Donate to: DrByte directly or to the Zen Cart team as a whole

    Remember: Any code suggestions you see here are merely suggestions. You assume full responsibility for your use of any such suggestions, including any impact ANY alterations you make to your site may have on your PCI compliance.
    Furthermore, any advice you see here about PCI matters is merely an opinion, and should not be relied upon as "official". Official PCI information should be obtained from the PCI Security Council directly or from one of their authorized Assessors.

  3. #3
    Join Date
    Jun 2007
    Posts
    438
    Plugin Contributions
    0

    Default Re: Slow query in mysql

    I find the order page and the customers page to load so slowly that I totally avoid them.

    My guess is that the query pulls ALL customers and ALL orders and then paginates rather than just pulling the first page.

    If I wasn't worried about overriding core pages I would totally change the customer and order pages and instead make them a landing page with a search field. Then only display the results of the search. That is how the pages get used anyway (at least by me and my team).

 

 

Similar Threads

  1. v139h Analyzing slow query report
    By jgold723 in forum General Questions
    Replies: 3
    Last Post: 5 Aug 2014, 01:49 AM
  2. Replies: 1
    Last Post: 22 Mar 2013, 03:39 AM
  3. Sometimes very slow pageload (long query times)
    By Doomm in forum General Questions
    Replies: 8
    Last Post: 13 Apr 2011, 06:30 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
disjunctive-egg
Zen-Cart, Internet Selling Services, Klamath Falls, OR