I think that each of you tried to find the own way to solve tasks in the learning process of a relational database management system, and not knowing that out there are various helpful features, which could speed up the queries at times and reduce the code size. In this article I want to share with you my experience, namely how to work with MySQL comfortably, often allowing the programmer to do the things that other databases would not be able to do. This material would be useful rather for those who just decided to delve into the wonderful world of queries, but maybe the experienced programmers will find something interesting for themselves here.
We all remember the classical explanation about the indexes in the database and how they make the task easier to find the right lines. I'm sure most of you visualizes something like this:
It becomes clear right away that it takes much less effort to find two or three right lines throughout the data. It is brilliant, easy, and clear.
Personally, I always thought that there is no room for any improvement regarding this method until I got familiar with the clustered indexes. It turned out that the non-clustered indexes are not that perfect as I though.
So here are some questions: What is exactly a clustered index? Why is it better than the non-clustered one? What’s going on with it in MySQL?
The database backup always has to be adjusted for the existing projects directly to the live production servers.
This situation is easily explained. At the beginning any project is empty and there is simply nothing to copy. In the phase of rapid development the heads of a few developers are busy working on the project and fixing critical bugs with the deadline a day before yesterday. And only when the project has reached its ultimate point, there comes the awareness that the main value of the system is accumulated database, and its failure would be a disaster.
This article is for people whose projects have already reached this point, but they haven’t been dumped on enough.
Somehow, I found a reference in the depths of documentation MySQL on dev.mysql.com/ about the fact that if MyISAM is used, we can get an increase in 5-7 times in reading speed from the table, if we read the data from the table ourselves. I wanted to verify this fact, so I did it. Let us see what came out of it.
What type of data will we read from MyISAM?
Format MyISAM has several formats (namely they are multiple storage formats of indexes and several types of data storage for the most common data, which are fixed and dynamic). I was interested to consider the very simple case: when a table has a fixed length of record, but it does not contain NULL fields and the type of queries that we are going to test. This is a simple SELECT * FROM tbl WHERE (condition) is without sharing the indexes and other tables (it is called full scan).
There is a special mode in MySQL that is designed for inputting the invalid data in a database. For example, in order to input in INT-field 2147483647 instead of 20000000000 or to fill the database with non-existent dates and cut lines. Well, you never know what fro this mode might be useful.
This mode is called a "traditional mode"
Although, at first glance it seems crazy and such a behavior is a historical reason.
The engine of MyISAM was a former standard engine for MySQL recently; it does not support the database transactions. If one thing falls off from a set of queries, then the database loses its integrity. In comparison with such risk an entry into the database of incorrect values was lesser evil.