* 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.