MySQL

News and interesting resources about MySQL announced on the last MySQL newsletter

In MySQL there is a parameter that enables logging of queries that take more than x seconds to execute calls Slow Queries. Basically what it does is put them in a log file and then check with mysqldumpslow. This mechanism may be useful to improve the overall performance of the database if we reduce the value of a progressive manner to the execution times of "leave" before declaring a query such as "slow" or slow query...

In MySQL, when you delete records from a table, space is reallocated automatically. It is as empty space and forming new attachments will advantage.

The problem is that if a table perform many DELETE operations, the physical space of the table will become increasingly fragmented and the performance is reduced.

In the MyISAM and InnoDB, OPTIMIZE TABLE command available to perform an optimization on any table that, among other things, performs an automatic defragmentation of the table.

It is highly recommended to use this command regularly especially on tables that are more statements of disposal of records.

As a precaution, keep in mind that during implementation, of course, the table is blocked. You have to remember when you are going to use with large tables and busy.

Supersimples The syntax is:

OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE mi_tabla1 [, mi_tabla2] ...;

To make a fragmented table optimization can be selected to have free space, probably as a result of DELETE statements:

MySQL, like most database managers, can easily modify the parameters that control memory sizes engaged in certain tasks, resource utilization, concurrency limits, etc.

Properly adjusting these parameters can be obtained many performance improvements, especially if the server / s of the database is not about resources, and if the SQL optimization can not be improved more.

I've recently made some basic settings in a MySQL database, so I take this opportunity to explain some of the process I followed for those who seek an easy way to make a first optimization of parameters in the database. This is not to say that this is the best way to do just that to me has worked out well;)

The first comment that can be very helpful to look in phpMyAdmin to the sections 'Show information about MySQL runtime' and 'Show MySQL system variables', normally accessible from the home page of the application.

The first displays information and statistics collected and maintained the system since its beginning. Pay special attention to the values of variables that are displayed in red, and the advice provided to the right of these values.

Please refer to the variables used to determine the current values of the parameters that could later be modified to improve performance.