There are two filesort algorithms in MySQL for sorting and retriving select queries results. 1. Original Filesort Algorithm : This method uses only the ORDER BY columns. (prior to MySQL 4.1) 2. Modified Filesort Alforithm : This method uses ORDER BY columns and the columns which are used in query. (MySQL 4.1 and newer version) … Continue Reading
Author Archives: Nilnandan Joshi
How to reset MySQL root password
1. Stop mysql service shell> /etc/init.d/mysql stop 2. Start to MySQL server without password. shell> mysqld_safe –skip-grant-tables & 3. Connect to mysql server using mysql client. shell> mysql -u root Welcome to the MySQL monitor. Commands end with ; or g.Your MySQL connection id is 107Server version: 5.1.49-1ubuntu8.1 (Ubuntu)Type ‘help;’ or ‘h’ for help. Type … Continue Reading
How to convert MySQL database from Latin1 to Utf8
Once client decided to switch database from Latin1 to UTF8. I was working on a Linux platform at the time but you can do the same thing in Windows too. Some people have a site that is using a Mysql database which uses the Latin1 character set and they want to convert their database to … Continue Reading
How "unlock tables" command work in MySQL
While working on MySQL locks, I have found one weird thing. I got one alert from Nagios that there are many queries which are locked. When I have checked “Show Processlist” I found that many queries were waiting for release read lock with below status. “Waiting for release of readlock” I have tried to run … Continue Reading
How to install Drupal 7.0 on Ubuntu 10.04
Drupal is a free and robust content-management system which runs on LAMP(Linux, Apache, MySQL and PHP) server. PostgreSQL and MariaDB are also compatible with with Drupal. Before start Drupal 7.0 installation, we have to install following packages related to LAMP. Here, I’m imagine that Ubuntu 10.04 is already installed 
– A web server. Apache … Continue Reading
How to setup CISCO VPN connection on Ubuntu 9.10
I found its really easy to setup CISCO VPN connection on Ubuntu 9.10 by using vpnc. There are 2 ways to setup it. 1. Use network-manager on system tray and set it there. 2. install vpnc on command line and setup it in shell prompt. I’m not happy with first option though its so easy … Continue Reading
Tools for MySQL Database
There are many tools available for MySQL DB. Some of them are really helpful for performing different kind of tasks for MySQL database. Like for database administration, we can use MySQL Enterprise Monitor (which is coming with MySQL Enterprise version and it is paid version too). http://www.mysql.com/products/enterprise/monitor.html In the Open source technology, one of the … Continue Reading
What to tune in MySQL Server after installation
Even though you can tune quite a lot of variables in MySQL Servers only few of them are really important for most common workload. After you get these settings right other changes will most commonly offer only incremental performance improvements. key_buffer_size – Very important if you use MyISAM tables. Set up to 30-40% of available … Continue Reading