Comments - Fitting index not used
Content reproduced on this site is the property of its respective owners,
and this content is not reviewed in advance by MariaDB. The views, information and opinions
expressed by this content do not necessarily represent those of MariaDB or any other party.
How many rows does your
article
table have?What do you mean by "analyze table but engine side, not table side"?
Do you think you can create a test case that we can run to repeat this behavior?
hi, I tried to collect some information about the reproductibility of it :
Here is a show table status of my full article table:
Here it is with less rows but still the problem:
If I try to drop "the big text column" I got this and the problem dissapear:
Today I reproduced the problem with:
Runs in 5 seconds with the wrong plan, 0.025s with the right one.
I still can't understand why the primary key on `thema` is used, as the join is:
And every other conditions are on article, itself providing nice multi-column-index on the said fields.
I even have an index on 'thema_id' on the article table, but better than that, I have (as already said) a `state, thema_id, published_date` on article. and also a `published_date` one in case the WHERE conditions get a too high proportion of lines and it's better to use the index to get already ordered data. So the query planner have a lot of space to play here with provided index, from my very personal POV :-p
As, as soon as I remove a column in article table, the problem dissapear, I can't easily provide a reproductible dump of it.
If you haven't already, please report this as an issue on JIRA - if you don't yet have a reproducible case, someone there may be able to help create one.