Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2007
    Posts
    72
    Plugin Contributions
    0

    Default db tuning - some queries not using defined indexes?

    Hi,

    I have a few queries that frequently drag down database peformance on our site. It's gotten so bad that it's causing the
    database to lock up and stop returning queries (I check processlist and see one query "writing to tmp" and then a bunch of queries with status "Locked". Then our pages stop being served (the whole site is db driven) and the site is essentially down until we reboot mysql....not ideal.

    One query in particular keeps showing up in our slow queries log and I looked at the explain plan. Looks like the query isn't using any of the indexes on the tables for some reason.

    Has anyone else run into issues with db queries like this, and do you have any suggestions to alleviate it? I already worked with our server guys to try to optimize my.cnf variables, and installed the query cache mod and that's helped a bit but the top level category pages are still really heavy. Explain plan is below...maybe there's something obvious I'm missing but none of the indexes are being used at all...

    Any suggestions/insight appreciated.

    thanks,
    Don

    -----

    select distinct p.products_id, p.products_image, p.products_tax_class_id, p.products_date_added, p.products_price,m.manufacturers_id ,m.manufacturers_name ,s.specials_new_products_price
    from products p
    left join specials s
    on p.products_id = s.products_id, products_to_categories p2c, products_description pd ,categories c,
    manufacturers m
    where p.products_id = p2c.products_id
    and p.products_id=pd.products_id
    and p.manufacturers_id=m.manufacturers_id
    and p2c.categories_id =7 XoR 8 and p2c.categories_id = c.categories_id
    and p.products_status = 1 order by pd.products_name limit 0,9;

    Code:
    mysql> explain select distinct p.products_id, p.products_image, p.products_tax_class_id, p.products_date_added, p.products_price,m.manufacturers_id ,m.manufacturers_name ,s.specials_new_products_price 
        -> from products p
        -> left join specials s
        -> on p.products_id = s.products_id, products_to_categories p2c, products_description pd ,categories c,
        -> manufacturers m
        -> where p.products_id = p2c.products_id
        -> and p.products_id=pd.products_id
        -> and p.manufacturers_id=m.manufacturers_id
        -> and p2c.categories_id =7 XoR 8 and p2c.categories_id = c.categories_id
        -> and p.products_status = 1 order by pd.products_name limit 0,9; 
    +----+-------------+-------+-------+---------------------+---------------------+---------+--------------------------+------+------------------------------------+
    | id | select_type | table | type  | possible_keys       | key                 | key_len | ref                      | rows | Extra                              |
    +----+-------------+-------+-------+---------------------+---------------------+---------+--------------------------+------+------------------------------------+
    |  1 | SIMPLE      | pd    | ALL   | NULL                | NULL                | NULL    | NULL                     |  725 | Using temporary; Using filesort    | 
    |  1 | SIMPLE      | m     | ALL   | NULL                | NULL                | NULL    | NULL                     |   90 |                                    | 
    |  1 | SIMPLE      | p     | ALL   | NULL                | NULL                | NULL    | NULL                     |  727 |                                    | 
    |  1 | SIMPLE      | s     | ref   | idx_products_id_zen | idx_products_id_zen | 4       | zc_db_prod.p.products_id |    1 |                                    | 
    |  1 | SIMPLE      | c     | index | NULL                | PRIMARY             | 4       | NULL                     |  159 | Using index; Distinct              | 
    |  1 | SIMPLE      | p2c   | index | NULL                | PRIMARY             | 8       | NULL                     | 2475 | Using where; Using index; Distinct | 
    +----+-------------+-------+-------+---------------------+---------------------+---------+--------------------------+------+------------------------------------+
    6 rows in set (0.00 sec)

  2. #2
    Join Date
    Aug 2005
    Posts
    26,503
    Plugin Contributions
    9

    Default Re: db tuning - some queries not using defined indexes?

    You have not provided the server base system????

    No matter, as tuning my.cnf is an item that hosts spend a huge amount of time on and are reluctant to share
    Zen-Venom Get Bitten
    Get Your Business Found

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

    Default Re: db tuning - some queries not using defined indexes?

    Do you mean the server/install specs?

    MySQL 5.0.90-community
    PHP Version 5.2.9
    Zen Cart 1.3.8
    Dedicated machine with a lot of RAM

    And all of the tables in the query have indexes on the primary keys and the columns in the where clauses, yet they aren't used. Maybe it's because of the LEFT JOIN, the distinct or some other parts of the query but I'm not sure. I tried googling around and didn't find a clear answer.

    -d

    Quote Originally Posted by kobra View Post
    You have not provided the server base system????

    No matter, as tuning my.cnf is an item that hosts spend a huge amount of time on and are reluctant to share

  4. #4
    Join Date
    Jan 2004
    Posts
    60,305
    Blog Entries
    4
    Plugin Contributions
    51

    Default Re: db tuning - some queries not using defined indexes?

    Something about that query is just wrong.
    Where did you get it from? As in ... I can't find that query within Zen Cart anywhere. What addon is using that?
    .

    Zen Cart - putting the dream of business ownership within reach of anyone!
    Donations always welcome: www.zen-cart.com/donate

    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.



 

 

Similar Threads

  1. Tuning the search field
    By dimar in forum General Questions
    Replies: 0
    Last Post: 1 Oct 2008, 11:52 AM
  2. some fine tuning help please.
    By cyrusli in forum Templates, Stylesheets, Page Layout
    Replies: 7
    Last Post: 12 Jun 2008, 10:27 PM
  3. 2 Seperate Indexes?
    By CnTGifts in forum General Questions
    Replies: 4
    Last Post: 22 Mar 2008, 07:49 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
  •