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.

Failed Setting Triangles Error

Recently, a reader sent me the following screenshot which shows a corrupted scene from my book’s demo project.

He also showed me the errors and warning he get when running the demo project:


Lightmap snapshot ‘LightmapSnapshot’ is inconsistent – 62 files out of 248 are missing. Realtime GI will be disabled. Please rebuild lighting for this scene.


Failed setting triangles. Some indices are referencing out of bounds vertices. IndexCount: 600, VertexCount: 11191

Failed setting triangles. Some indices are referencing out of bounds vertices. IndexCount: 96, VertexCount: 11191

This happens when Unity tries to convert the demo project to the latest version, which sometimes might mess up the files.


To solve this issue, reimport the mesh which has been corrupted by the conversion (in this case, terrain and rocks) by right clicking on the asset in Unity and choose “Reimport”, or delete the prefabs from the scene and place it again manually.

Once you have done the steps above, re-bake the lighting again and everything will work fine.

Minimal Qt Project

Today I was trying to experiment on how small can a Qt project get if I only use the essential modules and scrap away all the rest.

First I created a new Qt project by going to:
File -> New File or Project… -> Application -> Qt Widgets Application

When I build the new project I’m getting this empty window, which is expected:

Then I did the following:

1. Delete mainwindow.ui
2. Delete mainWindow.h and mainWindow.cpp
3. In main.cpp, remove QApplication and replace it with QGuiApplication instead.
4. Use QWindow instead of QMainWindow

The reason why I removed QApplication and QMainWindow is because they both rely on QtWidget module. This way we can reduce the size further by removing one more dependency. Now all I left are just 2 file: and main.cpp, and they now look like this:

TARGET = MyProject
SOURCES += main.cpp


#include <QGuiApplication>
#include <QWindow>

int main(int argc, char *argv[])
    QGuiApplication app(argc, argv);
    QWindow window;
    window.setTitle("Hello World!");
    window.resize(640, 480);;

    return app.exec();

Then I build the project again, which the result should look totally the same as before. However, we no longer need QtWidget.dll anymore which reduces the number of DLL files to just 5:

– libgcc_s_dw2-1.dll
– libstdc++-6.dll
– libwinpthread-1.dll
– Qt5Core.dll
– Qt5Gui.dll

The total size of entire executable folder, including all the DLL files mentioned above, is now only 11.3MB. Its still fairly large if compare to other application framework, but considering two things:

1. There are tons of functionalities in QtCore and QtGui alone, including all the helper classes and even OpenGL
2. All the DLL files in the entire Qt SDK is at the staggering size of 1.87GB in total

11.3MB is actually pretty damn impressive!

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!