0

I try to override the variable optimizer_switch and change rowid_filter=on to rowid_filter=off.

The official documentation does not provide an example.

Attempt #1:

/etc/mysql/mariadb.conf.d/70-optimizer.cnf
[GLOBAL]
optimizer_switch='rowid_filter=off'

Restart services:

sudo service mariadb restart
sudo service mysql restart

Attempt #2:

Same as #1 but I moved my file here /etc/mysql/conf.d/70-optimizer.cnf


I checked by logging in to mysql and executing SHOW VARIABLES LIKE "optimizer_switch";, and I get this at both attempts:

optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off

As you can see it is still turned on

What am I missing?


I tried setting it directly via SET GLOBAL optimizer_switch = 'rowid_filter=off'; but it is still on if I check.

2 Answers 2

1

I had to change the group from GLOBAL to mysqld

[mysqld]
optimizer_switch='rowid_filter=off'

Restart services:

sudo service mariadb restart
sudo service mysql restart
1

To Debug it , try these :

(D1) : Try setting it at mysql prompt & ensure that the value is getting updated.
If value is not getting update , Issue is something else.

(D2) You should try to set it at /etc/my.cnf or execute mysqld --help --verbose & check what configurations files are getting loaded.
It looks like the files you listed are not getting loaded.

(D3) Try setting some other option (at mysql prompt & via various configuration files) & ensure that the update goes through.
If no option goes through , Issue is something else.

(D4) Check your MySQL server Startup Command.
Ensure that it is not using --no-defaults which will force this Issue.

Based on your findings , we can move forward.
Current thinking is that these configuration files are not loaded , you have to check which files are actually loaded.

How to set it at MySQL Command Prompt or through conf file :

SET [GLOBAL|SESSION] optimizer_switch='command[,command]...';

set optimizer_switch='rowid_filter=off';

[mysqld]  
optimizer_switch=rowid_filter=off  

reference 1
reference 2
reference 3
reference 4

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .