Preface
Designing a MySQL database schema is an inevitable part of the career of every MySQL database administrator or even a developer. Developers and MySQL database administrators usually turn to the schema design side of MySQL to improve query performance, normalize their databases, add or drop certain indexes, modify columns, also for other things.
Generally, what one database administrator considers to be a "good" database design might not seem so perfect for another DBA. Everyone has their own set of preferences—depending on experience and other things—but in general, even while MySQL does move forward at a rapid pace, with improvements being made left and right, MySQL schema design practices do not change much.
How to Use Automatic Index Recommendations in PostgreSQL
In our last blog, we learned about the Need and Usage of Hypothetical Indexes in PostgreSQL. We can now "check" easily in a live environment, determine if some particular index will be helpful or not, and figure out how we get to know which index to test. To do this, you'll also need in-depth knowledge of indexing and experience in Postgresql. However, in PostgreSQL, we can get an automatic recommendation of indexes for specific queries by using three extensions hypog, pg_stat_statements, and pg_qualstats.
Let’s explore the practical uses of this feature in Postgres!
Hypothetical Indexes in PostgreSQL
In this blog, we will first cover what are hypothetical indexes and what is needed for these types of indexes. Secondly, we shall see the practical usage of these indexes.
Need of Hypothetical Indexes:
As the name suggests, these are not real indexes, these are hypothetical indexes i.e. they are virtual indexes that PostgreSQL query planner does not consider when running queries.
A Few Tips for Improving the Performance of Your Web Applications
In this post, we will mostly be talking about a few things that we should keep in mind while writing web APIs to get the best possible performance out of them. Most organizations work with large amounts of data that need to be processed every second. So, it's very important to keep things organized and optimized in order to deliver a great user experience. Let's discuss a few points to help you understand how we can work more of performance optimization.
Virtual Tables
If you're using MySQL or any other SQL-based database, using virtual tables is the best thing that you can do. Let's look at an example. I have three databases and in a single query I am fetching data from four different tables. This process may take some time. In that case, you can create virtual tables in your SQL databases, which are sometimes referred as views, and keep all the columns that you want to retrieve from all four tables. This time, instead of looking at all four tables, the query will pull everything into a single table. This will help to reduce the response time.
How Database B-Tree Indexing Works
When we think about the performance of a database, indexing is the first thing that comes to the mind. Here, we are going to look into how database indexing works on a database. Please note that here, architectural details are described referenced to SQLite 2.x database architecture. You can find out the backend implementation of SQLite 2.5.0 with tests, which is relevant to this post from https://github.com/madushadhanushka/simple-sqlite.
Read how overall SQLite database architecture composed in this DZone article.