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