MySQL is the very first database I learned when I was starting my journey. I have been using it for a while now, and there has been a lot of things that I wish I knew the day I started using MySQL. I decided to write this article in hopes that it will help other beginners trying to learn MySQL. These are the seven simple tips I have used along the way that helped me become successful with MySQL

. . .

Use Indexes

Using indexes properly can drastically speed up the query times of your application. The trade-off is well worth it. You will have a slight increase in storage requirements and a slight decrease in write speed. Being able to do this will help you stand out in an organization that has a database that is not indexed at all I have heard of some debate around this. Some only use it when they need to. I cannot give a thumbs up or down to that because, ultimately, it all comes down to preference. However, in my experience, I have noticed that I prefer using them.

Normalize your data and use joins

This practice involves storing your data separate from one another and then referencing that data with foreign keys. Now, imagine you are making a blog, so you have a post table, and in that post table, you have a post_id and a title. At this point, it might be tempting to store just the author name. Instead of that, why not create an author’s table and then store the author_id in the post table? I mean, rather than just querying on the post table, you can query on the post table and then join the author’s table onto the post table. By doing this, you have set yourself up so that when you add additional fields to the author’s table, it will be available immediately in the query. Another upside is that if a piece of data changes for an author, you don’t have to update anything in the posts.

Use of InnoDB

There are only two storage engines with my SQL that people use a lot. And it is MyISAM and InnoDB. MyISAM is going not as widely used, so you are going to want to use InnoDB because it is an all-around better engine. Besides performing better and the fact that your data is more durable, you also have access to transactions. This is something that MyISAM does not have, and in 2020/2021, that is something you need for your database.

Enforce data integrity at the database level

For this, you are going to want to use things like unique constraints and cascade deletes. A lot of times, it is compelling to do things in your application that you should be doing in your database. Cascading deletes can be best demonstrated in the earlier used post and author’s table example. Imagine you have an author_id x. If you were to delete that author, then it would look in the post table and delete all the posts with author_id x. The reason cascading deletes is crucial is because if you do not cascade delete, the post table will have posts that do not have an attached author. Keep in mind that you can also do this in your application. At the time you delete one record application, you could also query for all the posts and then delete those as well. It is best handled at the database level. This is because application logic can break but database constraints cannot.

Log queries in development

It is great to see queries happening in real-time. It can help you a lot while you are developing. To log queries, edit the “my.cnf” file like so:

Create a log file:

general_log_file = /var/log/mysql.log

Then do:

general_log = 1

This will make sure every time a query happens to the database, it will show up in that log. Just make sure you do not do this in production because it will add strain to your database.

Use consistent naming

It is typical to use short, easy to type field names, but whichever format you do choose do it everywhere. Variability and inconsistencies often lead to confusion, error and loss of time. It is not uncommon to come across tables and columns cluttered with abbreviations or shorthand that may be insightful to some, but enigmatic to others. Again, you do not have to adhere to someone else’s ideal conventions. Implement those that make sense to you and your team. Whether it be the addition of a prefix or inclusion of the application name, be sure those standards are consistent.

Use transactions for bulk insert

The reason for the use of transactions is because every time you insert a record, the table has to re-index. When using a transaction, you get one re-index at the end of the transaction. So if you insert 15,000 records, you get one re-index at the end of the 15,000. Whereas if you insert 15,000 individual records, you get 15,000 re-indexes. And the performance boost is significant. Without using transactions, you may get hundreds or thousands of records per minute. With using a transaction, you are going to get tens of thousands, if not hundreds of thousands of records per minute.

Example:

five re-indexes ( instead of doing this )

insert into value x (default, 1);
insert into value x (default, 2);
insert into value x (default, 3);
insert into value x (default, 4);
insert into value x (default, 5);

One re-index ( best way )

start transaction;
insert into value x (default, 1);
insert into value x (default, 2);
insert into value x (default, 3);
insert into value x (default, 4);
insert into value x (default, 5);
commit;

This has the added benefit of making all your data available immediately at the end. If you were to insert a hundred thousand individual records, the hundred thousand records would slowly insert, and they would be visible to your application. If you do it in one transaction, a hundred thousand records appear instantly at the end of the transaction.

When you get comfortable with MySQl, you will notice how easier it becomes to work with other databases.