5 MySQL Queries to Speed up Development

MySQL is one of the most widely used relational database management system. Even though we are in an era where document-oriented systems like MongoDB and other Big Data programs seem to be all the rage, but MySQL is still the choice of various popular websites such as Facebook, Quora, etc.

In this tutorial, I have covered some rarely-used MySQL queries which you can use to speed up your development.

Note: These MySQL queries may slow down your database if it has a large sum of data, but they will eventually reduce the code at the middle layer.

Here is the list:

• MySQL CASE
• INSERT into SELECT *
• INSERT ON DUPLICATE KEY UPDATE
• CREATE TABLE AS SELECT *
• UPDATE AND SELECT from same table

MySQL CASE

Have you heard about switching cases in MySQL? If not, this is supported inside MySQL. In practice, if you have a large sum of data (e.g. a million rows), then doing this switch casing won't be preferable. However, for learning purposes, you can use it.

Before I found out about this method, I used to run the SELECT query from the middle layer (e.g. Node.js), and then run the loop over the returned rows to perform calculations.

However, you can actually let MySQL do that for you. This way, you just need to refer to a particular column in the middle layer to know the result. Here is how to do it:

SELECT `Db`,`User`,
CASE  `Db` 
WHEN 'phpmyadmin'
THEN  'ITS SERVER DB'
ELSE
'ITS NOT SERVER DB'
END AS server
FROM  `db` 

Here is the sample output:

INSERT into SELECT *

This technique is a life savior for me. Before I found out about this method, it used to be a pain in the a*& to SELECT rows from DB, and then run one MySQL query call for each of those rows. That was when I was not sure I can simply insert into one table by selecting from another one. Simple and straight.

This query will definitely save lots of LOC and time on your end. Here is how to do it:

Example: Suppose you have a table user and you want to insert information from user to user_personal_information table

INSERT into `user_personal_information` 
(`coloumn_1`,`coloumn_2`) 
SELECT 
`coloumn_1`,`coloumn_2` 
FROM 
`user` 
WHERE 
`user`.`user_id` `user_personal_information`.`user_id`;

INSERT ON DUPLICATE KEY UPDATE

Ever encountered a situation where you need to INSERT some information in an existing table with a new set of data that contains duplicate records? What have you done at that time to solve the problem? Feel free to share your experiences in the comments!

At any rate, what I used to do in same situation is that I'd first find out the duplicate data using the SELECT query, and once I do, I'd manually copy them from one table to another temporary table, delete those records, and then run the INSERT query.

Maybe there were some other intelligent ways to do this, but I knew my method was time-consuming and not a smart approach. So here is a MySQL query to do this in one shot:

You can INSERT all sets of data, and on matching one, UPDATE it with incoming:

INSERT into `user_info` (`user_email`)  VALUES ('shahid@codeforgeek.com') ON DUPLICATE KEY
UPDATE `user_email` = 'duplicate-email'

In the scenario where we have a large sum of data and we want to merge it with existing data, then this kind of query will help a lot.

CREATE TABLE AS SELECT *

In a situation where you need to either create a backup of one table or just create a clone, you can efficiently do that using this query. Again, this may slow down MySQL a little so you wouldn't want to use it too frequently if you're working with a large system. However, if you're only going to use this once in a while, then you can go for it.

Here is how to clone a table in MySQL:

CREATE table `user_login_backup` AS SELECT * FROM `user_login`

UPDATE AND SELECT from same table

This is one of the most famous problems on Stack overflow. You may find a solution to this very quickly using Joins, but just for learning purposes I was trying to do in another way, which should be simple too.

I encountered a situation where I needed to UPDATE the record in one table by determining the data present in it. In a normal situation, we will SELECT the record first and prepare UPDATE query or you may do Join.

So, I figured it out how to do this in one query. What I did is combined the SELECT and UPDATE, but I Alias the SELECT query into one variable and used that in the WHERE condition.

Here is how to do this in MYSQL:

UPDATE `user_login` AS A1, (SELECT `user_email` FROM `user_login`) AS A2 SET 
`user_login`.`user_email` = 'shahid@codeforgeek.com'
WHERE
A2.`user_email` = `shahid123@gmail.com`

Conclusion

These are the top 5 queries which have really saved my time while writing code. If you know more, please share in the comments. 'Till then, happy coding!