MySQL is used to manage the databases; in the databases the data of the websites and web applications placed. MySQL is also known as a relational database because it stores the data in the form of an organized way by sorting the data in the tables. MySQL is popular because of its lot of features one of it is the capability to handle the huge data with a fast speed.
The MySQL database is convenient to use but there are some tips and tricks which helps the users to use MySQL with the better understanding, in this write-up, those tips and trick are being discussed.
What are the 30 important MySQL Tips and Tricks?
The most important 30 tips and trick which every MySQL user either beginner or advance level, should be familiar of are:
1. Storage engine in MySQL
MySQL uses two types of engines to store its data which are; InnoDb and MyISAM, both are good and efficient engines but InnoDB is little bit complicated to understand and use but it supports many advanced features like transactions therefore, MyISAM is recommended to the beginners to use when they are working on the MySQL. On the other hand MyISAM is isn’t scale very well which means it usually runs a single query at a single time.
2. Change password of MySQL
To change the password of the MySQL, first choose the database of MySQL by the following command in MySQL prompt:
USE mysql
Update the user set plugin by using the command:
update user set plugin="mysql_native_password" where User='root';
Set the password, we are setting “helloworld”, you can set something else on your own choice:
ALTER USER 'root'@'localhost' IDENTIFIED BY 'helloworld';
Flush the privileges by using the command:
FLUSH PRIVILEGES;
Exit the MySQL environment:
quit;
Restart the MySQL service by using the systemctl command:
$ sudo systemctl restart mysql
3. One column of the data should be of id column in MySQL
The tables containing the data should contain atleast one unique column, it will be much easy if this column is of “id” because the identity of any data cannot be same, this identity column can be helpful while making it a primary key. It is not compulsory to add the id column but its a tip to add a id column.
4. Dump files editing in MySQL
The dump files appear as the text files, so most peoples edit them using the text editors like nano, vim which corrupt the data of the mysqldump files. The only way is to leave these files the way they are to avoid any future inconvenience.
5. Partitioning in MySQL
In MySQL tables can be further divided into tables which enhances the processing speed of the queries as well as organize the data in a simplified manner. This division of tables in MySQL is known as partitioning and it is of five types; Range, List, Hash, Key, and Sub partitioning. Partitioning can be very helpful if you are managing a data on a large scale as it reduces the cost as well.
6. Avoid use of Null values in MySQL
Most users thinks the null values are the same as the empty sets; but actually there is a difference between both of them. The empty sets occupies no space whereas te null values occupies some physical space, which can create some disturbance while making comparisons. It is not recommended to declare columns with the Null values until there is a special need to use of it.
7. Insert bulk data using the CSV file in MySQL
If we have a bulk of data and we want to insert it in a table, instead of running a lot of queries to insert that data simply copy all the data in a CSV format file and exports its data to the table of the MySQL using a simple MySQL command.
Let us create a table of players_data:
CREATE TABLE players_data (players_id INT, players_name VARCHAR(50));
Open a text file, type the following data and save it with players_data.csv:
1,’John’
2,’Paul’
Run the following command in MySQL, to export the data of csv file in the MySQL table:
LOAD DATA INFILE 'c:/home/players_data.csv' INTO TABLE players_data FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;
To display the data in table use the command:
SELECT * FROM players_data;
8. Static tables in MySQL
Mostly readers will be surprised from the term of static tables that what is meant by it in MySQL? In MySQL, if you declare the column with data types that are having fixed lengths like INTEGER are will have the fixed size and the MySQL engine will handle it a faster way; repond the queries quickly as compared to other data types who have not fixed size like BLOB, VARCHAR.
9. Perform delete and insert statement on a large scale data in MySQL
In the live websites, there is a webtraffic available everytime, if we have to made some amendment on it especially if the website containing a huge data and we are going to perform either a delete action or insert action on the website, a huge probabilibty is there may the website will be halt or stopped because of which a great problem has to be faced to the web traffic.
To avoid this, it is recommended to perform Delete and Insert actions on a huge data of live websites using the while or any other loop statement and giving a pause of few seconds, the Apache server can execute the statement and handle the web traffic easily.
The example of the script of writing a code for such type of purpose is:
while (1) {
mysql_query("DELETE FROM logs WHERE log_date <= '2020-10-01' LIMIT 20000");
if (mysql_affected_rows() == 0) {
// done deleting
break;
}
// you can even pause a bit
usleep(10000);
}
10. Keep the things to the point in MySQL
In MySQL, the MySQL engine processed each and every single query of the table, even if you declare the column with a NULL, it will occupy some physical memory on computer memory. We have to avoid making things irrelevant like if we have know the columns will have the entries of characters not more than three, then there is no need to define the column with the data type VARCHAR, likewise, if we only need DATE, then there is no need to use the function of DATETIME().
Keep the thing to the point, so it will help the MySQL engine to process the queries quickly which in return is your own time benefit.
11. Finding the current time and date in MySQL
While working in daily routine. Many times a point comes when we need to find out the time and date; no need to find out the calendar and clock for it, MySQL provides a function to find them, using the command:
SELECT now();
12. Finding Length of strings in MySQL
We can also find the length of the strings used in MySQL using its build-in function, Length(), to understand the use of its function, run the following statement in MySQL:
SELECT length('Hello, this is Linuxfoss');
13. Join one or more strings together in MySQL
If you have more than one string placed in stored in different places and you want to join all of them together, you can use the concat() function in MySQL:
SELECT concat('Hello,’ ‘this is ’ ‘Linuxfoss’);
14. To capitalize and lower the fonts in MySQL
MySQL also present the functions of the upper() and lower() by which we can convert the alphabets upper to lower and lower to upper case, for example:
SELECT upper('Hello, this is Linuxfoss');
Similarly,
SELECT lower (‘HELLO, THIS IS LINUXFOSS’);
15. Taking Absolute of values in MySQL
Another useful tip is the use of “abs” function, it takes the absolute of the provided values for example:
SELECT abs (-22);
16. Rounding off numbers in MySQL
We have the numbers in the figures like 22.987, we want to round off it to 23, for this in MySQL we have a build-in function of round(), which is used as:
SELECT round(22.987);
17. Extracting the specific values in MySQL
If we want to extract something like we want to extract only year from the now() function then we can use the extract() with it like:
SELECT extract(year from now());
18. Finding out greatest and least values in MySQL
We can find out the greatest value from the list of number by using the function of greatest, for example:
SELECT greatest (10,20,70,50,100,90);
Similarly, we can find out the least one by using the function of least():
SELECT least (10,20,70,50,100,90);
19. Finding power of values in MySQL
If you are the mathemations then the MySQL is good for you as it facilitates you with many built-in functions used in the Maths like you can find out the power of any complex number:
SELECT power(44, 2);
20. Taking square root in MySQL
Like many other mathematic functions, you can also find out the square root of any number using the function of MySQL:
SELECT sqrt(81);
21. Relationships of the tables
In MySQL, tables can be relate with eachother, there are two types of tables; parent tables and children tables. A parent table should be unique from whic many children tables can be derived. These relationships can be shown with the help of the primary and foreign keys.
22. Display the limited output in MySQL
In MySQL, we have the tables containing the billions of rows; if we want to display the rows, it is not a good practice to display the entire table, use the LIMIT clause to display the required number of rows:
SELECT * FROM players_data LIMIT 3;
In the above command, we only displayed the three rows of the table.
23. Delete the data of table in MySQL
We can delete the data of the table in MySQL using the DELETE FROM statement, for example, we have a table in the database, players_data, we want to delete its data:
DELETE FROM players_data;
To verify the data of the player_data has been deleted, execute the command:
SELECT * FROM players_data;
24. A temporary table can be created in a MySQL
In MySQL, we can create a temporary table which can deleted when the database is switched off, this temporary table is created to store the data for a time being. We can create a table using a command:
CREATE TEMPORARY TABLE my_data (ids INT,name VARCHAR(50));
25. Control the access of the databases in MySQL
The MySQL contain a lot of databases, to manage these database, more than one users can be needed and they should be granted access to the specific database which they are going to be managed. To grant the access of the database a command should be run in the MySQL terminal whose general syntax is:
GRANT ALL PRIVILEGES ON [database name].* TO ‘new_user_name’@’localhost’;Replace the database name with the database name whose access you want to grant to the user, and replace the new_user_name with the actual user name.
26. Rename the tables in MySQL
The tables in the MySQL are rename by using the RENAME statement, for example we have a table in our database with the name of Customer_data, we want to rename it with Sales_data:
RENAME TABLE Customer_data to Sales_data;
The table has been renamed successfully.
27. Extract the Not Null values in MySQL
To find out the not null values from the table, we can use the COALESCE(), it will extract and return the first not null value from the table or the data given to it. For example:
SELECT COALESCE (null,6,3);
28. Find out the Null values in MySQL
We can use the IS NULL query to find out the null values from the tables in MySQL, for example, we have a table with the name of phone_directory_2, whose data can be displayed using:
SELECT * FROM phone_directory_2;
To extract the null values from the name column this table, we use the command:
SELECT name, phone_number,email FROM phone_directory_2 WHERE Name IS NULL;
29. Monitor your processes in MySQL
Run the queries actively and keep a eye on the execution of all the queries, a single ignored mistake can make you suffer with a lot of pain in future.
30. Save the log table in MySQL
Keep a check and balance of the log table which contains the history of the executed queries of the MySQL, and if the data of this log table is huge, simply move this table to the other directory or path to keep a low burden on the MySQL engine.
Conclusion
MySQL is used to handle and manage the data of large websites because it provides support to comprehensive application development. MySQL is used by UBER, Netflix, Shopify, and Twitter companies for the management of the data of their servers or databases. In this write-up, we have explained the 30 top MySQL tips and tricks which are very helpful if you are using MySQL.
TUTORIALS ON LINUX, PROGRAMMING & TECHNOLOGY
