Code Muri

The following diagram shows three different memory categories that server allocates.

Now check each item in more detail.

Query Cache

The query cache stores results of SELECT queries so that if the identical query is received in future, the results can be quickly returned.

Queries are examined in a case-sensitive manner, so

SELECT * FROM t

is different to 

select * from t

Comments are also considered and can make the queries differ, so

/* retry */ SELECT * FROM t

is different to

/* retry 2 */ SELECT * FROM t

Thread cache

By default, connection manager threads associate each client connection with a thread dedicated to it that handles authentication and request processing for that connection. Manager threads create a new thread when necessary but try to avoid doing so by consulting the thread cache first to see whether it contains a thread that can be used for the connection. When a connection ends, its thread is returned to the thread cache if the cache is not full.

The thread cache has a size determined by the thread_cache_size system variable. The default value is 0 (no caching), which causes a thread to be set up for each new connection and disposed of when the connection terminates.

(http://dev.mysql.com/doc/refman/5.5/en/connection-threads.html)

** I need to write more items that were not covered here **

Both MyISAM and InnoDB tablespaces are binary portable from one host to another if two conditions are met:

• Both machines must use two's-complement integer arithmetic
• Both machines must use IEEE floating-point format or else the tables must contain no floating-point columns (FLOAT or DOUBLE)

[mysqld]
lower_case_table_names=1

To sum up,

MyIsam 

• Both machines must use two's-complement integer arithmetic
• Both machines must use IEEE floating-point format or else the tables must contain no floating-point columns (FLOAT or DOUBLE)

A dirty read occurs when a transaction is allowed to read data from a row that has been modified by another running transaction and not yet committed.

A non-repeatable read occurs, when during the course of a transaction, a row is retrieved twice and the values within the row differ between reads.

A phantom read occurs when, in the course of a transaction, two identical queries are executed, and the collection of rows returned by the second query is different from the first. The phantom reads anomaly is a special case of Non-repeatable reads.

The following command will show:

To get query execution plan, explain is used. but not just for this, explain is more useful to get the table information such as column list and its detail.

mysql> explain [schema_name].[table_name]

or

mysql > explain table_name

+-------------+----------+------+-----+---------+----------------+
| Field       | Type     | Null | Key | Default | Extra          |
+-------------+----------+------+-----+---------+----------------+
| ID          | int(11)  | NO   | PRI | NULL    | auto_increment |
| Name        | char(35) | NO   |     |         |                |
| CountryCode | char(3)  | NO   |     |         |                |
| District    | char(20) | NO   |     |         |                |
| Population  | int(11)  | NO   |     | 0       |                |
+-------------+----------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

describe is same with this.

+-----------------+---------------------+------+-----+---------+-------+
| Field           | Type                | Null | Key | Default | Extra |
+-----------------+---------------------+------+-----+---------+-------+
| TABLE_CATALOG   | varchar(512)        | NO   |     |         |       |
| TABLE_SCHEMA    | varchar(64)         | NO   |     |         |       |
| TABLE_NAME      | varchar(64)         | NO   |     |         |       |
| TABLE_TYPE      | varchar(64)         | NO   |     |         |       |
| ENGINE          | varchar(64)         | YES  |     | NULL    |       |
| VERSION         | bigint(21) unsigned | YES  |     | NULL    |       |
| ROW_FORMAT      | varchar(10)         | YES  |     | NULL    |       |
| TABLE_ROWS      | bigint(21) unsigned | YES  |     | NULL    |       |
| AVG_ROW_LENGTH  | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_LENGTH     | bigint(21) unsigned | YES  |     | NULL    |       |
| MAX_DATA_LENGTH | bigint(21) unsigned | YES  |     | NULL    |       |
| INDEX_LENGTH    | bigint(21) unsigned | YES  |     | NULL    |       |
| DATA_FREE       | bigint(21) unsigned | YES  |     | NULL    |       |
| AUTO_INCREMENT  | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_TIME     | datetime            | YES  |     | NULL    |       |
| UPDATE_TIME     | datetime            | YES  |     | NULL    |       |
| CHECK_TIME      | datetime            | YES  |     | NULL    |       |
| TABLE_COLLATION | varchar(32)         | YES  |     | NULL    |       |
| CHECKSUM        | bigint(21) unsigned | YES  |     | NULL    |       |
| CREATE_OPTIONS  | varchar(255)        | YES  |     | NULL    |       |
| TABLE_COMMENT   | varchar(2048)       | NO   |     |         |       |
+-----------------+---------------------+------+-----+---------+-------+
21 rows in set (0.01 sec)

MySQL Workbench had nice feature to make SQL script to create a schema from existing database but it was not easy to find where it is.

1. Start MySQL Workbench

2. Select "Create EER Model From Existing Database" in the 'Data Modeling' section.

  (If you already have existing EER model, select it)

 * Enter required information and select Schema to reverse engineer

3. If EER Diagram is opened without any problem, Select "File > Export > Forward Engineer SQL CREATE Script..." like the following :

4. Select any option as you wish from the dialog and proceed the steps until the finish button is shown like the following. 

5. It's the SQL script we want.