MySQL Forums
Forum List  »  Optimizer & Parser

Re: MySQL 5.1.31 -- Same query scans widely different # of rows
Posted by: Blake Harps
Date: April 08, 2011 10:45AM

* If you do not think 12 is a correct estimate you should do an ANALYZE TABLE.

12 is fairly close. There are 15 unique values for LINE_OF_BUSINESS_ID.

* If 12 is a good estimate number of unique LOB_IDs, but there are much fewer than 30k/12 number of rows with LINE_OF_BUSINESS_ID=11, you could also modify the query with a FORCE INDEX [1], but this will make it more difficult for the MySQL optimizer to adapt to changes in your data.

mysql> select count(*) from LISTING where LINE_OF_BUSINESS_ID=11;
| count(*) |
|     2109 | 
1 row in set (0.00 sec)

So, while not exactly 30k/15 it seems to be close enough.

* Yet another possibility is to set max_seeks_for_key to a low value. This will make it less likely that table scan is chosen instead of the index.

I was definitely unaware this was configurable. Thanks for the heads up on this.

If possible, can you show EXPLAIN output for a problematic query as well?
Which storage engine do you use?

We are using InnoDB. I am currently attempting to get an explain for one of the abnormal queries; however, we have been unable to reproduce them in a test environment. I have a script in place that will capture the explain output for the query in our production environment if we see query time go above a certain threshold.

BTW, it would be easier to read queries and explain output if you could post it in a pretty-printed format inside code-tags, something like this:

My apologies. I will remember to format my queries/output appropriately in the future.

A thing to note is that ACCEPTING_LOCATION, LISTING & CUST_SDPO are all updated or inserted into to varying degrees during the day. However, the times of heavy insertion do not correlate with our spikes in rows_examined. At times the insert activity is two/three hours before the spikes, and at other times insert activity does not cause a spike at all.

Options: ReplyQuote

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.