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 web developer needs a console, but not that much that he/she should drop everything and start reading thick books about Linux. That's why I have learned occasionally some console tricks, and many of my coworkers do the same. I will reveal a few useful secrets without which I cannot live anymore.
1) Use ssh keys!
I discovered the keys a long time, but regularly there are people who never heard about them. SSH keys allow adapting the connection once, and then passwords do not have to be stored to all sites in a notepad.
$ ssh-keygen -t dsa
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.
It is funny, but when a programmer is developing a product, he rarely thinks about the question: Could 2000 people press simultaneously one button at the same time? It turns out they can. Curiously enough the most engines were written by such programmers that do not work well under heavy loads. Who would have thought that just one additional INSERT or unwritten index or a recursive function curve could raise significantly the load averages.
This article will describe how the developers of the project managed to squeeze out of a single server with the Pentium 4 HT / 512Mb RAM the maximum performance, holding simultaneously 700 users on the forum and 120000 on tracker. Also, it will give some details about Highload.
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).
Let us learn how to scale your application without having any
Experience, it is very difficult. Now there are many websites that are devoted to these issues, unfortunately, there is no solution that is suitable for all cases. We still need to find solutions ourselves, which are suitable for our requirements. Just as I do.
Several years ago, my boss came to me and said: “We have a new project for you, namely to transfer a website, which already has 1 million visitors per a month. You need to move this website and make sure that traffic could grow in the future without any problems”. I was already an experienced programmer, but I did not have any experience in the field of scalability. I had to learn scalability in the hard way.
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.
The study of data recovery method from accidentally deleted database of MySQL.
Here are CentOS 5.6 x86_64 and mysql 5.0.77
There was created testing database with a couple of tables as MyISAM and InnoDB, as well as a couple of stored procedures to test their recovery:
DROP DATABASE prod;
CREATE DATABASE prod;
CREATE TABLE table1 (
v VARCHAR (50)
PRIMARY KEY (id)
) ENGINE = MyISAM;
High-loaded project (website) is not necessarily a popular social net, video hosting or MMORPG. The easiest way deeply to improve the website’s requirements for the hardware is transferring storage of sessions in the database. In this article we will discuss how to store data in the database and at the same time does not cut on the performance. Using a small amount of RAM we can quite well save the time of CPU. We're talking about situation when memcached is not available and other special resources of caching.
Magic MEMORY tables
DBMS MySQL database system realizes the type of tables that are stored in memory permanently and always are available for a short time. This is MEMORY, there is a synonym HEAP. The second name is older; therefore, it is preferable to use the first one.
Comparing with MyISAM or InnoDB, this format is a very limited, but it handles well the storage of operational data. Traditionally, we will give its pluses and minuses, we start with the pluses: