optimizer_switch in MariaDB

I’ve been spending some time looking through the new features of MariaDB 5.3, and one of the big items is an improved and configurable optimizer. Much of the functionality of the new optimizer is controlled using the optimizer switch variable [http://kb.askmonty.org/en/mariadb-53-optimizer_switch]. There’s been some discussion (see [http://s.petrunia.net/blog/?p=61] and [https://lists.launchpad.net/maria-developers/msg04142.html]) about splitting optimizer switch into multiple separate variables, but for now we seem to be stuck with this:

mysql 5.3.3-MariaDB-rc (root) [test]> select @@optimizer_switch;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@optimizer_switch                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=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=off,table_elimination=on |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql 5.3.3-MariaDB-rc (root) [test]> select length(@@optimizer_switch);
+----------------------------+
| length(@@optimizer_switch) |
+----------------------------+
|                        547 |
+----------------------------+
1 row in set (0.00 sec)

547 characters! Ouch. What do you do if you want to get the value of a single one of these switches? I wrote a function to help:

mysql 5.3.3-MariaDB-rc (root) [test]> create function get_optimizer_switch(switch_name char(32)) returns char(3) return
     substring_index(substring_index(substring(@@optimizer_switch from locate(switch_name,@@optimizer_switch)),',','1'),'=','-1');
Query OK, 0 rows affected (0.00 sec)

mysql 5.3.3-MariaDB-rc (root) [test]> select get_optimizer_switch('derived_with_keys');
+-------------------------------------------+
| get_optimizer_switch('derived_with_keys') |
+-------------------------------------------+
| on                                        |
+-------------------------------------------+
1 row in set (0.01 sec)

That was still a little bit annoying, so I wrote a stored procedure, too.

DROP TEMPORARY TABLE IF EXISTS optimizer_switch_tmp;
DROP PROCEDURE IF EXISTS get_optimizer_switches//
CREATE PROCEDURE get_optimizer_switches () BEGIN
        DECLARE i INT DEFAULT 1;
        DECLARE n INT DEFAULT 1;
        DECLARE c VARCHAR(64);

        CREATE TEMPORARY TABLE optimizer_switch_tmp (switch_name char(64), val char(3));

        WHILE i < LENGTH(@@optimizer_switch) AND n <> 0 DO
                SET n = LOCATE(',', SUBSTRING(@@optimizer_switch FROM i));
                IF n = 0 THEN SET n = LENGTH(@@optimizer_switch)-i+2; END IF;
                INSERT INTO optimizer_switch_tmp VALUES (
            SUBSTRING_INDEX(SUBSTRING(@@optimizer_switch FROM i FOR n-1),'=',1), 
            SUBSTRING_INDEX(SUBSTRING(@@optimizer_switch FROM i FOR n-1),'=',-1)
        );
                SET i = i + n;
        END WHILE;

        SELECT * FROM optimizer_switch_tmp;
        DROP TEMPORARY TABLE optimizer_switch_tmp;
END//

mysql 5.3.3-MariaDB-rc (root) [test]> call get_optimizer_switches;
+-------------------------------+------+
| switch_name                   | val  |
+-------------------------------+------+
| index_merge                   | on   |
| index_merge_union             | on   |
| index_merge_sort_union        | on   |
| index_merge_intersection      | on   |
| index_merge_sort_intersection | 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     | off  |
| table_elimination             | on   |
+-------------------------------+------+
26 rows in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql 5.3.3-MariaDB-rc (root) [test]> 

If you know or can think of a better way, let me know in the comments.