Optimization in MySQL

[Calan]

I want to know more about the optimization in MySQL. I am having some basic concepts in it. Optimizing the use of a database is an extremely complex topic, as it is influenced by a lot of variables. There are concepts that are generally applicable to relational databases, and others that are specifically related to a RDBMS, depending on its characteristics. Of course, as long as our applications are small, and as much data, and the number of users accessing the database. I am sure that some of members must be having an idea about it. Please share that with me.

[Alejandro]

The first level of optimization where you can watch what is on the server and its configuration. The server administrator can set their values through the configuration file, or with options at boot time, or modifying a live server. The SQL statement SHOW VARIABLES allows us to display the values of all variables in use on the server (although not all have a direct influence on performance). Obviously it would be very long to explain the meaning of all the variables, it is also necessary for time to get used to assess the impact of each on the operation of the server. We will limit ourselves to say then that the first to be considered for optimization are key_buffer_size and table_cache: the first is the amount of memory space that is used by MySQL to keep values in memory of the indexes of MyISAM tables, in to limit access to the disk (can be set at around 25% of the total memory for a machine on which MySQL is the main application), while the second indicates the number of tables that the server can keep open at once. Reached this number, MySQL will close a table every time he needs to open another.

[pancham]

A trick that can save you time on all the instructions sent to the server is to use a simple system of permits: essentially, completely avoid to give permissions at the table level or column, and merely give permissions on the database. In fact, if the tables and tables_priv columns_priv mysql database does not contain data, MySQL will not have to go every time to check the permissions on them.

[Mecurtis]

The second level of optimization on the structure of databases, that is the way they are designed tables. Here are some suggestions:

• MyISAM tables on which are carried out frequent updates are faster if you do not have variable-length rows, and of course you must keep in mind that using fixed-length rows can have the drawback of wasting space, so you have to write a review which of the two issues is a priority;
• MyISAM tables can be quite slow if they have frequent updates and are read by slow query, in which case you should consider the possibility of changing storage engine
• try to minimize the occupation of space, because this allows the server to read more data with a disk access: what is always, therefore, assessed the field smaller and not adaptable to your data please fit a larger one (for example, for integer values, a field MEDIUMINT occupies 3 bytes while it takes 4 INT: so if you do not need more than 16 million use a MEDIUMINT values instead of an INT leads to savings of 25%), and also try to always declare NOT NULL columns, so you need to save space indicator of NULL values: at least, declared that a column can be NULL only if you really need;
• the primary key of a table should be as short as possible, to make immediate identification of a line

[Khushal]

In the second level of optimization on the structure of databases, the following are some more ways for designed tables,

• Indices are perhaps the most important factor in the optimization of a table: they are essential for fast reads, in particular, the columns that refer to other tables (foreign keys) and those used for research the query should always be indexed, but we must consider that the presence of indexes speeds up the reading but slow writing (in fact, the indexes are kept up to date), so it is important to find the right balance between the needs
• if you need to index text fields, it would be good to limit the number of characters included in the index, for example, if you have a field of 50 characters, but the first 10 are already sufficient to have a distinct range of values between them, they index only 10 result in a significant reduction on the size of the index;
• when you need to store binary data (eg images), you should save it to disk and not on the database, merely insert a reference to the filesystem in the table: this should allow for more speed
• data that are part of a table should be in third normal form, there are cases where it can be convenient accept redundancies, where this involves significant performance improvements