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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.