GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

ase explain this explain rows examined different in slow log.

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • ase explain this explain rows examined different in slow log.

    Sorry for type in title - can't edit. ase is Please.

    Maybe someone can explain?

    My server has been choking lately on this command - from the slow log

    # Query_time: 658.179910 Lock_time: 0.000112 Rows_sent: 11 Rows_examined: 3292981
    select t.id, t.ebay_item_id, t.ebay_user_id, t.ebay_trans_id, t.buyer_ebay_user_id, t.sold_time, t.paid_time, t.ship_time, t.price, t.quantity, t.ship_service, t.tracking_number, t.listing_type ,a.title, a.site_id, a.country_code, a.ebay_category_id, a.sku, a.picture_url
    FROM ebay_trans as t, auctions as a
    WHERE t.auction_id=a.id and t.ssb_user_id=39113 and a.id IN ( select id from auctions where ssb_user_id=39113 and (title like '%silver%' or sku like '%silver%')) ORDER BY t.sold_time desc LIMIT 20 ;

    So i ran explain on it to see what could be done.
    +----+--------------------+----------+-----------------+------------------------+-----------+---------+-----------------------------+------+-------------+
    | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
    +----+--------------------+----------+-----------------+------------------------+-----------+---------+-----------------------------+------+-------------+
    | 1 | PRIMARY | t | index | auction_id,ssb_user_id | sold_time | 4 | NULL | 6200 | Using where |
    | 1 | PRIMARY | a | eq_ref | PRIMARY | PRIMARY | 4 | ssbebay_alprod.t.auction_id | 1 | |
    | 2 | DEPENDENT SUBQUERY | auctions | unique_subquery | PRIMARY,ssb_user_id | PRIMARY | 4 | func | 1 | Using where |
    +----+--------------------+----------+-----------------+------------------------+-----------+---------+-----------------------------+------+-------------+


    The thing I don't understand is what rows means I guess. I am not even going to question the silly choice of a key it chose (ssb_user_id is the best choice)
    But if 6200 shows in the 'rows' column (max retrieved by the query, right?), why does the slow log show 3 million?

    Mysql 5.0.89



    Last edited by jmarcv; 08-25-2014, 12:41 PM.
Working...
X