"Limit" could mean "Max number of rows in a table".

"Offset" mean pick from row number (not to be confused by primary key value or any field data value)

The output of a mysqldump operation is a lightly commented file containing sequential SQL statements that are compatible with the version of MySQL utilities that was used to generate it (with attention paid to compatibility with previous versions, but no guarantee for future ones). Thus, the restoration of a mysqldumped database comprises execution of those statements. Generally, this file

• DROPs the first specified table or view
• CREATEs that table or view
• For tables dumped with data (i.e. without the --no-data option)
  • LOCKs the table
  • INSERTs all of the rows from the original table in one statement
• UNLOCK TABLES
• Repeats the above for all other tables and views
• DROPs the first included routine
• CREATEs that routine
• Repeats the same for all other routines

The presence of the DROP before CREATE for each table means that if the schema is present, whether or not it is empty, using a mysqldump file for its restoration will populate or overwrite the data therein.

A stored routine is either a procedure or a function.

A procedure is invoked using a CALL statement and can only pass back values using output variables.

A function can be called from inside a statement just like any other function and can return a scalar value.

Views are virtual tables and do not contain the data that is returned. They can save you from writing complex queries again and again.

• Before a view is made its specification consists entirely of a SELECT statement. The SELECT statement cannot contain a sub-query in the FROM clause.
• Once a view is made it is used largely just like a table and can be SELECTed from just like a table.

You have to create views, when you want to restrict few columns of your table, from the other user.

• For example: In your organization, you want your managers to view few information from a table named-"Sales", but you don't want that your software engineers can view all fields of table-"Sales". Here, you can create two different views for your managers and your software engineers.

Performance. VIEWs are syntactic sugar. However there performance may or may not be worse than the equivalent query with the view's select folded in. The Optimizer attempts to do this "fold in" for you, but is not always successful. MySQL 5.7.6 provides some more enhancements in the Optimizer. But, regardless, using a VIEW will not generate a faster query.

Starting from MySQL 5.7.8, MySQL ships with a JSON type. Lots of devs have been saving JSON data in text columns for a log time but the JSON type is different, the data is saved in binary format after validation. That avoids the overhead of parsing the text on each read.

Pivot query creation in MySQL relies upon the GROUP_CONCAT() function. If the result of the expression that creates the columns of the pivot query is expected to be large, the value of the group_concat_max_len variable must be increased:

set session group_concat_max_len = 1024 * 1024; -- This should be enough for most cases

Configuration happens in one of 3 ways:

• command line options
• the my.cnf configuration file
• setting variables from within the server

Command Line options takes the form mysqld --long-parameter-name=value --another-parameter. The same parameters can be placed in the my.conf configuration file. Some parameters are configurable using system variables from within MySQL. Check the official documentation for a complete list of parameters.

Variables can have dash - or underscore _. Spaces may exist around the =. Large numbers can be suffixed by K, M, G for kilo-, mega-, and giga-. One setting per line.

Flags: Usually ON and 1 are synonymous, ditto for OFF and 0. Some flags have nothing after them.

When placing the settings in my.cnf, all settings for the server must be in the [mysqld] section, so don't blindly add settings to the end of the file. (Note: For tools that allow multiple mysql instances to share one my.cnf, the section names may be different.)

To view a List of MySQL Users, we use the following command :

SELECT User,Host FROM mysql.user;

UNION does not use multiple CPUs.

UNION always* involves a temp table to collect the results. *As of 5.7.3 / MariaDB 10.1, some forms of UNION deliver the results without using a tmp table (hence, faster).

Over the years, InnoDB has improved to the point where it is almost always better than MyISAM, at least the currently supported versions. Bottom line: Don't use MyISAM, except maybe for tables that are truly temporary.

One advantage of MyISAM over InnoDB remains: It is 2x-3x smaller in space required on disk.

When InnoDB first came out, MyISAM was still a viable Engine. But with the advent of XtraDB and 5.6, InnoDB became "better" than MyISAM in most benchmarks.

Rumor has it that the next major version will eliminate the need for MyISAM by making truly temporary InnoDB tables and by moving the system tables into InnoDB.

• Lack of an AUTO_INCREMENT id for this table -- The PK given is the 'natural' PK; there is no good reason for a surrogate.
• MEDIUMINT -- This is a reminder that all INTs should be made as small as is safe (smaller ⇒ faster). Of course the declaration here must match the definition in the table being linked to.
• UNSIGNED -- Nearly all INTs may as well be declared non-negative
• NOT NULL -- Well, that's true, isn't it?
• InnoDB -- More effecient than MyISAM because of the way the PRIMARY KEY is clustered with the data in InnoDB.
• INDEX(y_id, x_id) -- The PRIMARY KEY makes it efficient to go one direction; the makes the other direction efficient. No need to say UNIQUE; that would be extra effort on INSERTs.
• In the secondary index, saying just INDEX(y_id) would work because it would implicit include x_id. But I would rather make it more obvious that I am hoping for a 'covering' index.

You may want to add more columns to the table; this is rare. The extra columns could provide information about the relationship that the table represents.

You may want to add FOREIGN KEY constraints.

• RANGE partitioning. This type of partitioning assigns rows to partitions based on column values falling within a given range.

• LIST partitioning. Similar to partitioning by RANGE, except that the partition is selected based on columns matching one of a set of discrete values.

• HASH partitioning. With this type of partitioning, a partition is selected based on the value returned by a user-defined expression that operates on column values in rows to be inserted into the table. The function may consist of any expression valid in MySQL that yields a nonnegative integer value. An extension to this type, LINEAR HASH, is also available.

• KEY partitioning. This type of partitioning is similar to partitioning by HASH, except that only one or more columns to be evaluated are supplied, and the MySQL server provides its own hashing function. These columns can contain other than integer values, since the hashing function supplied by MySQL guarantees an integer result regardless of the column data type. An extension to this type, LINEAR KEY, is also available.

mysqlimport will use the name of the imported file, after stripping the extension, to determine the destination table.

UNION DISTINCT is the same as UNION; it is slower than UNION ALL because of a de-duplicating pass. A good practice is to always spell out DISTINCT or ALL, thereby signaling that you thought about which to do.

Replication is used to copy[Backup] data from one MySQL database server to one or more MySQL database servers.

Master -- The MySQL database server, which is serving data to be copied

Slave -- The MySQL database server, copies data which is served by Master

With MySQL, replication is asynchronous by default. This means slaves do not need to be connected permanently to receive updates from the master. For example, if your slave is switched OFF or not connected with master and you are switching slave ON or connect with Master at a later time, then it will automatically synchronize with the Master.

Depending on the configuration, you can replicate all databases, selected databases, or even selected tables within a database.

Replication Formats

There are two core types of replication formats

Statement Based Replication (SBR) -- which replicates entire SQL statements. In this, the master writes SQL statements to the binary log. Replication of the master to the slave works by executing that SQL statements on the slave.

Row Based Replication (RBR) -- which replicates only the changed rows. In this, the master writes events to the binary log that indicate how individual table rows are changed. Replication of the master to the slave works by copying the events representing the changes to the table rows to the slave.

You can also use a third variety, Mixed Based Replication (MBR). In this, both statement-based and row-based logging is used. Log will be created depending on which is most appropriate for the change.

Statement-based format was the default in MySQL versions older than 5.7.7. In MySQL 5.7.7 and later, row-based format is the default.

When you need to handle time information for a worldwide user base in MySQL, use the TIMESTAMP data type in your tables.

For each user, store a user-preference timezone column. VARCHAR(64) is a good data type for that column. When a user registers to use your system, ask for the time zone value. Mine is Atlantic Time, America/Edmonton. Yours might or might not be Asia/Kolkata or Australia/NSW. For a user interface for this user-preference setting, the WordPress.org software has a good example.

Finally, whenever you establish a connection from your host program (Java, php, whatever) to your DBMS on behalf of a user, issue the SQL command

 SET SESSION time_zone='(whatever tz string the user gave you)'

before you handle any user data involving times. Then all the TIMESTAMP times you have install will render in the user's local time.

This will cause all times going in to your tables to be converted to UTC, and all times coming out to be translated to local. It works properly for NOW() and CURDATE(). Again, you must use TIMESTAMP and not DATETIME or DATE data types for this.

Make sure your server OS and default MySQL time zones are set to UTC. If you don't do this before you start loading information into your database, it will be almost impossible to fix. If you use a vendor to run MySQL, insist they get this right.

Recovering and resetting the default root password using this method is applicable only for MySQL 5.7+

For most cases, working with a 1:M relationship requires us to understand Primary Keys and Foreign Keys.

A Primary key is a column in a table where any single row of that column represents a single entity, or, selecting a value in a primary key column results in exactly one row. Using the above examples, an EMP_ID represents a single employee. If you query for any single EMP_ID, you will see a single row representing the corresponding employee.

A Foreign Key is a column in a table that corresponds to the primary key of another different table. From our example above, the MGR_ID in the EMPLOYEES table is a foreign key. Generally to join two tables, you'll join them based on the primary key of one table and the foreign key in another.