Database Performance Tips
I would like to share with you some tips when it comes to increase the performance of your database , across the years I have seen a lot of mistakes me including and these becomes very expensive in any business.
Database Design
The main thing is it’s all about how you design your database for your needs , bad planning brings big problems.
To design you need to think how you write and query the data you have in the database, I have seen SQL queries with so many joins , this is not so much of a bad thing but this depending on how many queries you have and amount of data this can be a bottleneck for any database. The best thing is always to normalize the tables you have so you don’t need to any joins in different tables.
Indexes
Indexes are your friend make sure to use them wise, indexes are declared on the columns that you most use on a table on your queries and will allow to accelerate your queries , but assigning all your columns as indexes will not help at all , your queries need to be written to query the columns you most use and assign the indexes on that . By making your queries faster will make sure not to pile up queries on your database for a longer time.
JOINS
You might not have any other way around then joining one table with another , but you need to be sure if it is really necessary to use Joins, by using joins this will perform poor on your queries depending on how much data you are querying and how often, when using JOINS make sure if there is no other way around. Again on Joins it’s essential to use indexes to make queries perform faster.
Myisam vs Innodb
If you using mysql make sure your tables are not to in MyIsam, this a very old db engine, new versions of mysql by default will be in Innodb engine, Myisam can be very lightweight and certain people still say it’s best performant compared to innodb but this is not true.
Myisam it does less operations and validations compared to Innodb but it’s all about how you configure your database settings properly, the problem of Myisam is with writes it locks the tables is writing and reads cannot go through until the writes are finished, while Innodb is row based lock, this means if you write something to a table on the rows you are inserting and updating are locked but all other data you can read without problems.
Stored Procedures:
Stored procedure comes in great hand when you have thethe most common queries, which will be pre-compiled after the first use and making a bit more faster aswell it is easier to maintain then using queries across different files on your code.
Partition:
Using partition is only good when you can no longer optimize more, if you create a partition on a table this will split the row results into different partitions , example imagine you have results with 2018 , 2017, 2017 dates, and you query most 2018 dates, by creating partition on the dates you will query less results and will make a lot faster your queries. Partitions are good but only if you have no other choice then doing so, if there is data you no longer need is better you clear it from your tables to avoid having heavy tables.
Sharding:
This as partition is another topic that if you have no other way to optimize your database and you need to scale then you can shard the data, just for you to understand , sharding is pretty much like a partition of the data but this is spread into other server instances, this means pieces of your data is in different servers and not replicated on same server. I am not a fan of this and I try to avoid using sharding at all costs , the problem here is if your queries you need to fetch results where the shards can be in more then one server and one of the server is down then this will compromise the results while the server is down.
Conclusion: To conclude with Partition and Sharding you only need to get to here if you have no other choice, this is why it is important to only have data that you really need to use and the rest you clear from your database, aswell making sure to optimize your queries and designing properly your database structure.
You can always aswell optimize your database servers for mysql you can play with the INNODB Buffer pool Size and other configurations I will write on other day.
