Hi Blake,
> What would cause the optimizer to be taking wildly different routes for the same query?
The index statistics may be out of date, the problematic queries may have less selective conditions etc.
A guess (assuming that the EXPLAIN output is from a query that is OK):
From the explain output we see that LISTING is accessed first on the LIST_LOB_FK index. The table has ~30k rows but this index has a cardinality of only 12 which means that there are only 12 or so different values in this index. That is probably dangerously close to the number where the optimizer prefers a table scan. Possible things to do:
* If you do not think 12 is a correct estimate you should do an ANALYZE TABLE.
* 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.
* 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.
If possible, can you show EXPLAIN output for a problematic query as well?
Which storage engine do you use?
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:
explain
select customersd0_.CUST_SDPO_ID as CUST1_59_,
customersd0_.VERSION as VERSION59_,
...
from CUST_SDPO customersd0_
inner join ...
where listingdis2_.EFFECTIVE_BEGIN_DATE<='2011-03-30 14:46:23' and
listingdis2_.EFFECTIVE_END_DATE>'2011-03-30 14:46:23' and
...
[1] Citing from the manual
http://dev.mysql.com/doc/refman/5.1/en/index-hints.html "You can also use FORCE INDEX, which acts like USE INDEX (index_list) but with the addition that a table scan is assumed to be very expensive."