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.
hi, I tried to collect some information about the reproductibility of it :
Here is a show table status of my full article table:
Name: article Engine: InnoDB Version: 10 Row_format: Compact Rows: 241787 Avg_row_length: 14450 Data_length: 3493855232 Max_data_length: 0 Index_length: 230604800 Data_free: 7340032 Auto_increment: 448191 Create_time: 2015-08-19 10:10:30 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULLHere it is with less rows but still the problem:
Name: article Engine: InnoDB Version: 10 Row_format: Compact Rows: 50780 Avg_row_length: 14062 Data_length: 714080256 Max_data_length: 0 Index_length: 41156608 Data_free: 4194304 Auto_increment: 448164 Create_time: 2015-09-04 12:41:30 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULLIf I try to drop "the big text column" I got this and the problem dissapear:
Name: article Engine: InnoDB Version: 10 Row_format: Compact Rows: 68839 Avg_row_length: 5297 Data_length: 364707840 Max_data_length: 0 Index_length: 29605888 Data_free: 0 Auto_increment: 448164 Create_time: 2015-09-04 12:43:33 Update_time: NULL Check_time: NULL Collation: utf8_general_ci Checksum: NULLToday 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.