OK,

Since I am no MySQL master, I took a while to figure out how to modify the optimizer_search_depth for a running MySQL instance. I am running Ubuntu 8.x Server on my box. The reason I needed to do modify the optimizer_search_depth is that I didn’t want to do long drawn out searches across my database from my applications. So I read up on this variable called optimizer_search_depth. Finding in-depth documentation on it was not easy. I am however documenting here for my and others’ future reference.

To modify the optimizer_search_depth, you need to modify the /etc/mysql/my.cnf file. This is the main MySQL configuration file. It doesn’t matter where you put it, just that it isn’t commented out. The default installation on Ubuntu seems to be 62 for optimizer_search_depth. I tuned it down to 4. It runs incredibly fast compared to before. If the variable isn’t there already-which in the my.cnf on Ubuntu is not, just insert in the typical name/value pair association like so: optimizer_search_depth = 10 . Restart your MySQL server and/or box and the config should take just fine. To check type in the command ‘mysqladmin variables’ and check for ‘optimizer_search_depth’.

:Vaughn

Tags: depth, config, file, x server, default, check, default installation, command