The MySQL Configuration File: my.cnf
This file, entitled my.cnf, stores default startup options for both the server and for clients. Correct configuration of this file can go a long way towards optimizing MySQL, as various memory buffer settings and other valuable options can be set here.
Interestingly, the scope of this file can be set according to its location. The settings will be considered global to all MySQL servers if stored in /etc/my.cnf. It will be global to a specific server if located in the directory where the MySQL databases are stored (/usr/local/mysql/data for a binary installation, or /usr/local/var for a source installation). Finally, its scope could be limited to a specific user if located in the home directory of the MySQL user (~/.my.cnf). Keep in mind that even if MySQL does locate a my.cnf file in /etc/my.cnf (global to all MySQL servers on that machine), it will continue its search for a server-specific file, and then a user-specific file. You can think of the final configuration settings as being the result of the /etc/my.cnf, mysql-data-dir/my.cnf, and ~/.my.cnf files.
MySQL Table Types
MySQL supports various of table types or storage engines to allow you to optimize your database. The table types are available in MySQL are:
- ISAM
- MyISAM
- InnoDB
- BerkeleyDB (BDB)
- MERGE
- HEAP
The most important feature to make all the table types above distinction is transaction-safe or not. Only InnoDB and BDB tables are transaction safe and only MyISAM tables support full-text indexing and searching feature. MyISAM is also the default table type when you create table without declaring which storage engine to use. Here are some major features of each table types:
ISAM
ISAM had been deprecated and removed from version 5.x. All of it functionality entire replace by MyISAM. ISAM table has a hard size 4GB and is not portable.
MyISAM
MyISAM table type is default when you create table. MyISAM table work very fast but not transaction-safe. The size of MyISAM table depends on the operating system and the data file are portable from system to system. With MyISAM table type, you can have 64 keys per table and maximum key length of 1024 bytes.
InnoDB
Different from MyISAM table type, InnoDB table are transaction safe and supports row-level locking. Foreign keys are supported in InnoDB tables. The data file of InnoDB table can be stored in more than one file so the size of table depends on the disk space. Like the MyISAM table type, data file of InnoDB is portable from system to system. The disadvantage of InnoDB in comparison with MyISAM is it take more disk space.
BDB
BDB is similar to InnoDB in transaction safe. It supports page level locking but data file are not portable.
MERGE
Merge table type is added to treat multiple MyISAM tables as a single table so it remove the size limitation from MyISAM tables.
HEAP
Heap table is stored in memory so it is the fastest one. Because of storage mechanism, the data will be lost when the power failure and sometime it can cause the server run out of memory. Heap tables do not support columns with AUTO_INCREMENT, BLOB and TEXT characteristics.
MySQL Data Types
Numeric Data Types
You can find all SQLÂ standard numeric types in MySQL including exact number data type and approximate numeric data types including integer, fixed-point and floating point. In addtion, MySQLÂ also supports BIT data type for storing bit field values. Numeric types can be signed or unsigned except BITÂ type. The following table shows you the summary of numeric types in MySQL:
| Numeric Types | Description |
| TINYINT | A very small integer |
| SMALLINT | A small integer |
| MEDIUMINT | A medium-sized integer |
| INT | A standard integer |
| BIGINT | A large integer |
| DECIMAL | A fixed-point number |
| FLOAT | A single-precision floating-point number |
| DOUBLE | A double-precision floating-point number |
| BIT | A bit field |
String Data Types
In MySQL, string can hold anything from plain text to binary data such as images and files. String can be compared and searched based on pattern matching by using LIKE clause or regular expression. The table below shows you the string data types in MySQL:
| String Types | Description |
| CHAR | A fixed-length non-binary (character) string |
| VARCHAR | A variable-length non-binary string |
| BINARY | A fixed-length binary string |
| VARBINARY | A variable-length binary string |
| TINYBLOB | A very small BLOB (binary large object) |
| BLOB | A small BLOB |
| MEDIUMBLOB | A medium-sized BLOB |
| LONGBLOB | A large BLOB |
| TINYTEXT | A very small non-binary string |
| TEXT | A small non-binary string |
| MEDIUMTEXT | A medium-sized non-binary string |
| LONGTEXT | A large non-binary string |
| ENUM | An enumeration; each column value may be assigned one enumeration member |
| SET | A set; each column value may be assigned zero or more set members |
Date and Time Data Types
MySQL provides types for date and time and combination of date and time. In addition, MySQL also provide timestamp data type for tracking last change on a record. If you just want to store the year without date and month, you can use YEAR data type. Here is the table which showing MySQL date and type data types:
| Date and Time Types | Description |
| DATE | A date value in ‘CCYY-MM-DD’ format |
| TIME | A time value in ‘hh:mm:ss’ format |
| DATETIME | A date and time value in ‘CCYY-MM-DD hh:mm:ss’ format |
| TIMESTAMP | A timestamp value in ‘CCYY-MM-DD hh:mm:ss’ format |
| YEAR | A year value in CCYY or YY format |
Spatial Data Types
MySQLÂ support many spatial data types as below table which contains various kind of geometrical and geographical values.
| Spatial Data Types | Description |
| GEOMETRY | A spatial value of any type |
| POINT | A point (a pair of X Y coordinates) |
| LINESTRING | A curve (one or more POINT values) |
| POLYGON | A polygon |
| GEOMETRYCOLLECTION | A collection of GEOMETRY values |
| MULTILINESTRING | A collection of LINESTRING values |
| MULTIPOINT | A collection of POINT values |
| MULTIPOLYGON | A collection of POLYGON values |
Creating Tables
To create table we use the CREATE TABLE statement. The typical form of SQL CREATE TABLE statement is as follows:
| 1 | CREATEÂ TABLEÂ [IFÂ NOTÂ EXISTS] table_name( | |
| 2 | column_list | |
| 3 | ) |
- MySQL supports IF NOT EXISTS after CREATE TABLE statement to prevent you from error of creating table which already exists on the database server.
- table_name is the name of table you would like to create. After that, you can define a set of columns which is usually in this form: column_name data_type(size) [NOT] NULL.
- You can specify the storage engine type you prefer to use for the table. MySQL supports various storage engines such as InnoDB, MyISAM… If you don’t explicit declare storage engine type, MySQL will use MyISAM by default.
In our classicmodels sample database, to create employees table, we can use the CREATE TABLE statement as follows:
| 01 | CREATEÂ TABLEÂ employees ( | |
| 02 | employeeNumber into(11) NOT NULL, | |
| 03 | lastName varchar(50) NOT NULL, |
| 04 | firstName varchar(50) NOT NULL, |
| 05 | extension varchar(10) NOT NULL, |
| 06 | email varchar(100) NOT NULL, |
| 07 | officeCode varchar(10) NOT NULL, |
| 08 | reportsTo int(11) default NULL, |
| 09 | jobTitle varchar(50) NOT NULL, | |
| 10 | PRIMARYÂ KEYÂ Â (employeeNumber) | |
| 11 | ); |
First, you specify table name employees after CREATE TABLE statement.
Then you list the columns of the table with their attributes such as data type, size, NOT NULL.
And finally you specify the primary key of the table, in this case the primary key is employeeNumber.
If the table has more than one primary key, you can seperate them by a comma. For example, the payments table has two primary keys customerNumber and checkNumber. You can create payments table by executing following query:
| 1 | CREATEÂ TABLEÂ payments ( | |
| 2 | customerNumber int(11) NOT NULL, | |
| 3 | checkNumber varchar(50) NOT NULL, |
| 4 | paymentDate datetime NOT NULL, |
| 5 | amount double NOT NULL, | |
| 6 | PRIMARYÂ KEYÂ Â (customerNumber,checkNumber) | |
| 7 | ); |
Note taht by default MySQLÂ uses MyISAM storage engine for the table it created.
Showing and Describing Tables in a Database
In order to show all tables in a database, you use SHOW TABLES statment. By executing theSHOW TABLES statement, MySQL will returns all tables’ name of the current selected database you’re working with.
| 1 | SHOW TABLES |
Here is the output of classicmodels database:
+————————-+
| Tables_in_classicmodels |
+————————-+
| customers              |
| employees              |
| offices                |
| orderdetails           |
| orders                 |
| payments               |
| productlines           |
| products               |
+————————-+
8 rows in set (0.00 sec)
In some cases, you need to see the table’s metadata, you can use DESCRIBE statement as follows:
| 1 | DESCRIBE table_name; |
For instance, we can describe employees table like below query:
| 1 | DESCRIBE employees; |
The output return from the database server:
+—————-+————–+——+—–+———+——-+
| Field         | Type        | Null | Key | Default | Extra |
+—————-+————–+——+—–+———+——-+
| employeeNumber | int(11)Â Â Â Â Â | NOÂ Â | PRI | NULLÂ Â Â |Â Â Â Â Â Â |
| lastName      | varchar(50) | NO  |    | NULL   |      |
| firstName     | varchar(50) | NO  |    | NULL   |      |
| extension     | varchar(10) | NO  |    | NULL   |      |
| email         | varchar(100) | NO  |    | NULL   |      |
| officeCode    | varchar(10) | NO  |    | NULL   |      |
| reportsTo     | int(11)     | YES |    | NULL   |      |
| jobTitle      | varchar(50) | NO  |    | NULL   |      |
+—————-+————–+——+—–+———+——-+
8 rows in set (0.02 sec)
dd