MySQL Forums
Forum List  »  Performance

Re: Planner taking a bad decission when having majority of NULLs
Posted by: Rick James
Date: May 08, 2012 09:16AM

> several times with identical results, but also because I had used SQL_NO_CACHE
Good.

> NULL values and -1 values with identical distributions. I mean, they perform differently because of their different plans.
Interesting. Two possible explanations for the difference in plans:
* The optimizer may treat NULL differently. (I don't know.)
* The "statistics" may have been different. (This is an occasional problem in InnoDB.)
* NULL sorts (I think) before all numeric values; would "-1" show up at the beginning, or in the middle of the values? (Such a diff could make a difference.)
> but I tested it several times going back and forward with NULLs and -1s values
* That probably means that there is something substantive. (But, I don't know what to make of it.)

Meanwhile, I will file your observation away, while watching for more evidence. I may learn something from this.

> notReplicableMultiSelect3
Very strange.
* only one column
* InnoDB, yet no PRIMARY KEY
* maybe formData2.formDataId should be AUTO_INCREMENT?

> Just for curiosity's sake, I tested what would happen if I ran that one, I mean, the one that SHOW WARNINGS shows. Surprisingly, it behaves in the "right" way, meaning it does not use the annoying index and it runs fast.
Interesting. Note that the EXPLAIN says what it feels like at the moment. It may change its mind when you run it again.

> lacking the JOINinig conditions
I think it decided the ON... could be turned into WHERE... without changing the semantics.

Alas, I wrote a lot of words in this reply, but probably did not say much.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Planner taking a bad decission when having majority of NULLs
1051
May 08, 2012 09:16AM


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.