auto_increment_increment & auto_increment_offset Both variables are use with master-to-master replication, and can be used to control the operation of AUTO_INCREMENT columns. Both variables have global and session values and each can assume an integer value between 1 and 65,535 inclusive. Setting the value of either of these two variables to 0 causes its value to be set to 1 instead. Attempting to set the...

Today I am going to explain the MySQL server configuration file variable. MySQL Server (mysqld) is the main program that does most of the work in a MySQL installation. When MySQL Server starts it uses the configuration file. The first thing you need to know is where MySQL gets configuration information: from command-line arguments and settings in its configuration file. On Unix-like systems, the configuration...

These are the two highly used string types in MySQL. Both store the character values. It’s very difficult to explain exactly how these values are stored on disk and in memory, because the implementations are storage engine-dependent (for example, Falcon uses its own storage formats for almost every data type). I am going to explain for MyISAM and InnoDB. VARCHAR VARCHAR as name represent variable character....

Right now MySQL Supports a large number of data types, and choosing the correct type to store your data is crucial to getting good performance. The following simple guidelines can help you make better choices, no matter what type of data you are storing: Smaller data type is better for performance. First of all try to use the smallest data type that can appropriately store and represent your data. Smaller data...

MySQL Performance Boost by Selecting the Right Table Engine Today I am going to explain you how the MySQL table engine affects the database performance.  While designing MySQL-based applications, you should decide which storage engine to use for storing your data. If you don’t think about this during the initial phase, you will possible face difficulties later in the process of development of your application....

Figuring out which queries are slow is usually easier than figuring out why they’re slow and making the necessary changes to fix them. The easiest way to track them is to let MySQL do some of the work for you. By enabling the slow query log, you instruct MySQL to log every query that takes longer than a specified number of seconds. In addition to the query, it also logs some other metadata. # Time: 090708...

Single-table syntax: DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name [WHERE where_condition] [ORDER BY ...] [LIMIT row_count] For the single-table syntax, the DELETE statement deletes rows from tbl_name and returns a count of the number of deleted rows. This count can be obtained by calling the ROW_COUNT() function. The WHERE clause, if given, specifies the conditions that identify which rows to delete....

Configuring a MySQL server is often just a matter of editing the configuration file to make any changes you need and then restarting the server. While that sounds rather simple, adjusting the server’s configuration is something you’re not likely to do on a daily basis. More likely, you’ve installed MySQL, configured it minimally or with the defaults, and then let it run. Most users never go...

Though most validation of data is generally controlled by applications, there are still ample situations where data is manipulated directly on the database. MySQL validation basically depends on the SQL modes of the server. Both INSERT and UPDATE statements can be run with the IGNORE option to allow for certain type of validation errors to go through. What are server SQL modes? Server SQL modes define what SQL...

MySQL operates in a networked environment using a client/server architecture. In other words, a central program acts as a server, and various client programs connect to the server to make requests. A MySQL installation has the following major components: MySQL Server, or mysqld, is the database server program. The server manages access to the actual databases on disk and in memory. MySQL Server is multi-threaded...