Not the answer you need?
Register and ask your own question!

ase explain this explain rows examined different in slow log.

jmarcvjmarcv EntrantCurrent User Role Beginner
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
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.