Killing MySQL Processes

Once in a while you might face problems related to MySQL being not responding. Most of the time it’s due to large database size or you’re running a select query on an un-indexed table. Instead of waiting for the process to complete (which may take hours), you can cancel the stalling processes using the following method.

First, open up another MySQL session and call the following query:


Next, you will see results like this:

| Id | User | Host | db | Command | Time | State | Info | Progress |
| 254772 | event_scheduler | localhost | NULL | Daemon | 2693 | Waiting on empty queue | NULL | 0.000 |
| 435342 | root | localhost | NULL | Query | 0 | Table lock | SHOW PROCESSLIST | 0.000 |

Look for the ID of the query which you wish to terminate and use it in the following query, for example:

KILL 435342;

That’s it, the process will then be terminated by MySQL. Hope it helps.

Import and Export Large MySQL Database

For most of the time, web-based database management control panel such as phpMyAdmin does the job well for importing and exporting backups. However, as your database grows larger and larger, it will reach to a point where it can no longer be done using any web-based control panels anymore.

For instance, our database has reached 500mb just a couple of months ago and phpMyAdmin would stop halfway when doing the job and showing a timeout message. It’s a standard for web server to stop loading a page and declare as timeout once it waited for a certain amount of time. You can of course change the web server configuration to make the timeout really really long but that is not practical and may break your web server.

A better way would be using the command line tools came together with MySQL. Let’s try to import a large database using the command line tool. The first thing you need to do is to open up the command prompt, or terminal. For Windows operating system, you need to access to the bin folder in your MySQL installation directory, for example:

cd C:\xampp\mysql\bin

Then, use the following command to import the SQL file to your database.

mysql -u username -p database_name < file.sql

Then, a message will pop out and ask for the database password. Insert the correct password and wait for a while until all the data is being loaded to the database. If you see your mysql directory is showing in the next line, it means the operation has be done. Your command prompt should look something like this:

Next, we will export our database using the command line tool. This time, we will be using the mysqldump utility. To export your database, type in this command line:

mysqldump -u username -p database_name > file.sql

If you don't specify a target destination for the file, it will be saved to the bin folder, in this case, C:\xampp\mysql\bin.

That's all! It's very easy and quick once you're familiar with the command line.

MySQL Tips

Count Item Quantity:

We can count the quantity of data contained in a table by using COUNT.

SELECT COUNT(*) AS count FROM employee

It can also be used to count the quantity of data that matches the condition you set.

SELECT COUNT(marriageStatus = 0) AS count FROM employee

This however, is really slow compare to the previous query because internally, MySQL already contains a static variable that tell you exactly the quantity so it doesn’t count it all over again. For the second query which uses condition, MySQL has to check the condition for each of the data and count it one by one before it’s able to tell you the quantity, therefore, very slow.

Combine Repeating Queries Into One:

We can combine queries that are being called repeatedly into 1 single query to speed up the performance. There are people who use UNION for this but that actually has plenty of limitation as well as not helping much in term of performance because we’re still sending tons of repetitive commands despite it’s sent in one go.

In-order to solve this issue, we combine the condition that we use to search the data into 1 single query by using OR. The resulting query looks like this:

SELECT * FROM employee where id = 1 OR id = 2 OR id = 3

For the application side, you can add all the conditions from an array using for loop, which look like this:

string query = "SELECT * FROM employee where ";
for (int i = 0; i < myArray.length(); i++)
    query += "id = " + + " OR";
query.mid(2); // Remove the last OR

From my personal experience, this can sometime speed up the query time up to 10x, depending on how big the data you're getting.

Join Multiple Tables Together:

Sometimes we want some portion of data from Table A, but also require another portion of data from Table B, Table C and so on. We want to join the data altogether into a single result so that it's straight forward and we don't have to do it multiple times. This can be achieved through INNER JOIN, LEFT JOIN and RIGHT JOIN. You can check out the differences between them on the internet, but I'll show you an example on how I use INNER JOIN:

SELECT,, FROM (SELECT id, name, companyID FROM employee AS employeeinfo) INNER JOIN company ON = employeeinfo.companyID

Replace URL in Your Content:

There are times where you might need to change all the URL in your site's contents, especially if you have just moved your website to a new domain, which means all the hyperlinks and images in your website will be broken. It's pain in the arse and almost impossible to fix if the contents of your website has grown into a massive pile of data.

MySQL actually provide a pretty handy function called REPLACE which does exactly this. For example, it will change the image link localhost/testsite/mylogo.png to which only replace the main URL in-front and not the image name with its extension. Here I show an example in WordPress' format:

UPDATE `wp_posts` SET `post_content` = REPLACE(post_content, 'localhost/testsite', '')

Search Using Keywords:

This is really straight forward, you just need to use LIKE and place your keyword between the two % symbols.

SELECT * FROM employee WHERE fullname LIKE %keyword_here%

Eliminate Duplicate Rows in a Result Set:

Sometimes a query may return duplicated results when you search through a table that serves as a log or history list but you only need one if the result ever exist. For example, I want to generate a list of users who logged in to my website this month. Because a user may log in multiple times in a month, so there may be duplicated rows in the result set. So, instead of something like this:

User Name Login Timestamp
John Doe 2015-08-01 12:30:02
John Doe 2015-08-01 1:20:14
John Doe 2015-08-01 4:30:22
Sally Smith 2015-08-01 4:31:06
John Doe 2015-08-01 5:30:06
Sally Smith 2015-08-01 5:45:02

What we want is just a distinct set of results:

User Name Login Timestamp
John Doe 2015-08-01 12:30:02
Sally Smith 2015-08-01 4:31:06

In order to filter out the results, we use DISTINCT in our query:


It's that simple!

Set MySQL Time Zone

Sometimes your MySQL database’s timezone is set to UTC time by default and does not follow your local timezone. In this case, you’ll need to manually change its configuration.

The configuration file can usually be found in mysql folder, which, depends on the operating system you’re running, could be installed in different directory.

Linux & Mac:

  • /usr/local/mysql/support-files/my.cnf
  • /etc/my.cnf
  • /etc/mysql/my.cnf


  • C:/AppServ/MySQL/my.cnf
  • C:/Program Files/MySQL/MySQL Server 4.1/my.ini

Once you found the config file, open it up with text editor and add this line to the end of the file:

default-time-zone = '+08:00'

In this example, I’m change the time zone to GMT+8.

Next, you need to restart MySQL in order to see the effect. You could do so by opening up command prompt and type in this command:


sudo service mysql restart


net stop MySQL
net start MySQL


sudo /usr/local/mysql/support-files/mysql.server restart

Now your MySQL should run in the correct time zone!