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 = " + myArray.at(i) + " 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 employeeinfo.id, employeeinfo.name, company.name FROM (SELECT id, name, companyID FROM employee AS employeeinfo) INNER JOIN company ON company.id = 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 yourwebsite.com/mylogo.png 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', 'yourwebsite.com')

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:

SELECT DISTINCT userName FROM loginHistory WHERE MONTH(logDate) = MONTH(CURDATE())

It's that simple!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.