MySQL explain Command

In this example, a table is created with a TIME column. It has 12 rows.

 

A select statement shows that the table has 3 rows with TIME > 1000. The explain command is then used to see how this statement works. The rows column in the output from the explain command is MySQL’s estimate of the number of rows which the query will need to examine. In the absence of an index, My SQL needs to do a full table scan so the answer is 12.

 

An index is then added to the table’s TIME column and the explain command is rerun. It uses the index and estimates that 4 rows will need to be examined to run the query:

 

--------------

create table t1

like information_schema.PROCESSLIST

--------------

 

Query OK, 0 rows affected (0.00 sec)

 

--------------

insert into t1 select * from

information_schema.PROCESSLIST

--------------

 

Query OK, 12 rows affected (0.00 sec)

Records: 12  Duplicates: 0  Warnings: 0

 

--------------

select count(*) from t1 where TIME > 1000

--------------

 

+----------+

| count(*) |

+----------+

|        3 |

+----------+

1 row in set (0.00 sec)

 

--------------

explain

select count(*) from t1 where TIME > 1000

--------------

 

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |   12 | Using where |

+----+-------------+-------+------+---------------+------+---------+------+------+-------------+

1 row in set (0.00 sec)

 

--------------

create index i on t1(TIME)

--------------

 

Query OK, 12 rows affected (0.01 sec)

Records: 12  Duplicates: 0  Warnings: 0

 

--------------

explain

select count(*) from t1 where TIME > 1000

--------------

 

+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+

| id | select_type | table | type  | possible_keys | key  | key_len | ref  | rows | Extra                    |

+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+

|  1 | SIMPLE      | t1    | range | i             | i    | 4       | NULL |    4 | Using where; Using index |

+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+

1 row in set (0.00 sec)

How to Copy Data from Oracle to MySQL

This example shows how to copy a table from Oracle to MySQL. First create a table in Oracle and add some data, including some null values and a date column:

 

SQL> create table andrews_table

  2  (first_name varchar2(10),

  3   dob        date,

  4   salary     number)

  5  /

 

Table created.

 

SQL> insert into andrews_table values

  2  ('Fred', '25-DEC-1980', null)

  3  /

 

1 row created.

 

SQL> insert into andrews_table values

  2  ('Jim', null, 10000)

  3  /

 

1 row created.

 

SQL> insert into andrews_table values

  2  (null, '01-SEP-1983', 20000)

  3  /

 

1 row created.

 

SQL> select * from andrews_table

  2  /

 

FIRST_NAME DOB           SALARY

---------- --------- ----------

Fred       25-DEC-80

Jim                       10000

           01-SEP-83      20000

 

SQL>

 

Write a query to extract the data to a file. Note the date format and the way that null values are represented as \N:

 

UNIX > cat extract.sql

set feedback off

set lines 100

set pages 0

set termout off

set trimspool on

spool extract

select nvl(first_name,'\N')||'|'||

       nvl(to_char(dob,'YYYY-MM-DD'),'\N')||'|'||

       nvl(to_char(salary),'\N')

from andrews_table

/

spool off

UNIX >

 

... and run the SQL:

 

SQL> @extract

SQL>

 

The output file will look like this:

 

UNIX > cat extract.lst

Fred|1980-12-25|\N

Jim|\N|10000

\N|1983-09-01|20000

UNIX >

 

Copy it to a machine running MySQL then you can load it into a table as follows:

 

--------------

create table andrews_table

(first_name varchar(10),

dob        date,

salary     int)

--------------

 

Query OK, 0 rows affected (0.01 sec)

 

--------------

load data local infile 'extract.lst'

into table andrews_table

fields terminated by '|'

lines terminated by '\n'

(first_name, dob, salary)

--------------

 

Query OK, 3 rows affected (0.00 sec)

Records: 3  Deleted: 0  Skipped: 0  Warnings: 0

 

--------------

select * from andrews_table

--------------

 

+------------+------------+--------+

| first_name | dob        | salary |

+------------+------------+--------+

| Fred       | 1980-12-25 |   NULL |

| Jim        | NULL       |  10000 |

| NULL       | 1983-09-01 |  20000 |

+------------+------------+--------+

3 rows in set (0.00 sec)

 

mysql>

MySQL db.opt File

When you create a database, you have to specify options such as the character set it will use:

 

mysql> create database andrew default charset utf8;

Query OK, 1 row affected (0.01 sec)

 

mysql>

 

These options are recorded in the db.opt file in the database directory:

 

UNIX > pwd

/usr/local/mysql/data/andrew

UNIX > cat db.opt

default-character-set=utf8

default-collation=utf8_general_ci

UNIX >

 

You can change them with the alter database command:

 

mysql> alter database andrew charset = dec8;

Query OK, 1 row affected (0.05 sec)

 

mysql>

 

... and the db.opt file will be updated:

 

UNIX > pwd

/usr/local/mysql/data/andrew

UNIX > cat db.opt

default-character-set=dec8

default-collation=dec8_swedish_ci

UNIX >

How to Add a Primary Key to a MySQL Table

In another post, I showed how to create a new table with a single-column primary key. This example shows how to add a multi-column primary key to an existing table: 

mysql> create table andrews_names

    -> (first_name varchar(10),

    ->  surname    varchar(10));

Query OK, 0 rows affected (0.06 sec)

 

mysql> alter table andrews_names

    -> add primary key

    -> (first_name, surname);

Query OK, 0 rows affected (0.30 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> insert into andrews_names

    -> values ('Andrew', 'Reid');

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into andrews_names

    -> values ('Andrew', 'Bloggs');

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into andrews_names

    -> values ('Joe', 'Reid');

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into andrews_names

    -> values ('Andrew', 'Reid');

ERROR 1062 (23000): Duplicate entry 'Andrew-Reid' for key 1

mysql> select *from andrews_names;

+------------+---------+

| first_name | surname |

+------------+---------+

| Andrew     | Bloggs  |

| Andrew     | Reid    |

| Joe        | Reid    |

+------------+---------+

3 rows in set (0.01 sec)

 

mysql>

Creating Indexes in MySQL

This example shows how to create single and multi-column indexes in MySQL. There appears to be nothing to stop you creating two indexes with the same name, as long as they are on separate tables. Then it shows how to list the indexes on a table. As always, click on the image to display it at its actual size and bring it into focus:

You can drop indexes as follows. Note that you have to include the table name in case there is more than one index with a given name:

 

--------------

drop index i on t1

--------------

 

Query OK, 0 rows affected (0.00 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

--------------

drop index i on t2

--------------

 

Query OK, 0 rows affected (0.01 sec)

Records: 0  Duplicates: 0  Warnings: 0

MySQL Analyze Command

You can analyze a table to update its statistics as follows:

 

--------------

create table andrews_table like

information_schema.PROCESSLIST

--------------

 

Query OK, 0 rows affected (0.01 sec)

 

--------------

insert into andrews_table select * from

information_schema.PROCESSLIST

--------------

 

Query OK, 12 rows affected (0.00 sec)

Records: 12  Duplicates: 0  Warnings: 0

 

--------------

analyze table andrews_table

--------------

 

+--------------------+---------+----------+----------+

| Table              | Op      | Msg_type | Msg_text |

+--------------------+---------+----------+----------+

| test.andrews_table | analyze | status   | OK       |

+--------------------+---------+----------+----------+

1 row in set (0.00 sec)

Killing a MySQL User Session

When you login to MySQL, you are given a connection id. In the example below, this is 40:

 

UNIX > mysql -u andrew -p

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 40

Server version: 5.5.19 MySQL Community Server (GPL)

 

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

 

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 

mysql>

 

root can see this as the id column in the output from show processlist:

 

mysql> select user();

+----------------+

| user()         |

+----------------+

| root@localhost |

+----------------+

1 row in set (0.00 sec)

 

mysql> show processlist;

+----+--------+-----------+------+---------+------+-------+------------------+

| Id | User   | Host      | db   | Command | Time | State | Info             |

+----+--------+-----------+------+---------+------+-------+------------------+

| 40 | andrew | localhost | NULL | Sleep   | 2327 |       | NULL             |

| 41 | root   | localhost | NULL | Query   |    0 | NULL  | show processlist |

+----+--------+-----------+------+---------+------+-------+------------------+

2 rows in set (0.00 sec)

 

mysql>

 

... and root can use it to kill the session:

 

mysql> select user();

+----------------+

| user()         |

+----------------+

| root@localhost |

+----------------+

1 row in set (0.00 sec)

 

mysql> kill 40;

Query OK, 0 rows affected (0.00 sec)

 

mysql> show processlist;

+----+------+-----------+------+---------+------+-------+------------------+

| Id | User | Host      | db   | Command | Time | State | Info             |

+----+------+-----------+------+---------+------+-------+------------------+

| 41 | root | localhost | NULL | Query   |    0 | NULL  | show processlist |

+----+------+-----------+------+---------+------+-------+------------------+

1 row in set (0.00 sec)

 

mysql>

 

When the user accesses his session, he is to told that it has lost its connection. He is then allowed to reconnect without supplying a password and is given a new connection id:

 

mysql> use test;

No connection. Trying to reconnect...

Connection id:    42

Current database: *** NONE ***

 

Database changed

mysql>

MySQL verbose Option

The verbose option tells MySQL to display SQL before running it. This is useful when using the source command to run SQL from a file. You can see what I mean in the example which follows. Here is a file containing a single SQL statement:

 

UNIX > cat count_star.sql

select count(*) from mysql.user;

UNIX >

 

When you run it from MySQL with the verbose (-v) option, the SQL appears before the output it produces:

 

UNIX > mysql -u root -p -v

Enter password:

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 53444

Server version: 5.0.67 Source distribution

 

Reading history-file /usr/local/mysql/.mysql_history

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

 

mysql> source count_star.sql

--------------

select count(*) from mysql.user

--------------

 

+----------+

| count(*) |

+----------+

|       18 |

+----------+

1 row in set (0.00 sec)

 

mysql> exit

Writing history-file /usr/local/mysql/.mysql_history

Bye

UNIX >

MySQL Character Set (part 2)

Go to part 1 

You can specify a database’s character set when you create it:

 

mysql> create database andrew

    -> default character set utf8;

Query OK, 1 row affected (0.00 sec)

 

mysql> show create database andrew;

+----------+-----------------------------------------------------------------+

| Database | Create Database                                                 |

+----------+-----------------------------------------------------------------+

| andrew   | CREATE DATABASE `andrew` /*!40100 DEFAULT CHARACTER SET utf8 */ |

+----------+-----------------------------------------------------------------+

1 row in set (0.00 sec)

 

mysql>

 

... and you can alter it subsequently as follows:

 

mysql> alter database andrew charset = latin1;

Query OK, 1 row affected (0.00 sec)

 

mysql> show create database andrew;

+----------+-------------------------------------------------------------------+

| Database | Create Database                                                   |

+----------+-------------------------------------------------------------------+

| andrew   | CREATE DATABASE `andrew` /*!40100 DEFAULT CHARACTER SET latin1 */ |

+----------+-------------------------------------------------------------------+

1 row in set (0.00 sec)

 

mysql>

MySQL auto_increment Clause

This post looks slightly different to earlier ones as I created it with the verbose option and ran the SQL from a file using the source command. MySQL allows you to increment column values automatically with the auto_increment clause. You can only auto increment one column per table and that column must be used as a key:

 

--------------

create table andrews_table(

id int(4) auto_increment,

location varchar(10))

--------------

 

ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key

--------------

create table andrews_table(

id int(4) auto_increment,

location varchar(10),

primary key(id))

--------------

 

Query OK, 0 rows affected (0.00 sec)

 

When you insert rows into the table, you do not need to supply values for the column which is automatically incremented. Note how three rows are being inserted simultaneously:

 

--------------

insert into andrews_table (location)

values ('Redhill'), ('Crawley'), ('Horsham')

--------------

 

Query OK, 3 rows affected (0.00 sec)

Records: 3  Duplicates: 0  Warnings: 0

 

By default, it looks as if the auto incremented column starts at 1 and goes up by 1 each time. I will look at this in more detail in future posts:

 

--------------

select * from andrews_table

--------------

 

+----+----------+

| id | location |

+----+----------+

|  1 | Redhill  |

|  2 | Crawley  |

|  3 | Horsham  |

+----+----------+

3 rows in set (0.01 sec)

 

MySQL allows you to look at the value automatically inserted into the last row. For a multi-row insert, this returns the value inserted into the first row created by that insert:

 

--------------

select last_insert_id()

--------------

 

+------------------+

| last_insert_id() |

+------------------+

|                1 |

+------------------+

1 row in set (0.00 sec)

 

--------------

insert into andrews_table (location)

values ('Croydon')

--------------

 

Query OK, 1 row affected (0.00 sec)

 

--------------

select * from andrews_table

--------------

 

+----+----------+

| id | location |

+----+----------+

|  1 | Redhill  |

|  2 | Crawley  |

|  3 | Horsham  |

|  4 | Croydon  |

+----+----------+

4 rows in set (0.00 sec)

 

--------------

select last_insert_id()

--------------

 

+------------------+

| last_insert_id() |

+------------------+

|                4 |

+------------------+

1 row in set (0.00 sec)

 

mysql>

Inserting the Current Date in a MySQL Table

You can insert the current date into a MySQL table using curdate(), current_date() or current_date:

 

--------------

create table andrews_table (col1 date)

--------------

 

Query OK, 0 rows affected (0.02 sec)

 

--------------

insert into andrews_table select curdate()

--------------

 

Query OK, 1 row affected (0.00 sec)

Records: 1  Duplicates: 0  Warnings: 0

 

--------------

insert into andrews_table select current_date()

--------------

 

Query OK, 1 row affected (0.00 sec)

Records: 1  Duplicates: 0  Warnings: 0

 

--------------

insert into andrews_table select current_date

--------------

 

Query OK, 1 row affected (0.00 sec)

Records: 1  Duplicates: 0  Warnings: 0

 

--------------

insert into andrews_table values (curdate())

--------------

 

Query OK, 1 row affected (0.00 sec)

 

--------------

insert into andrews_table values (current_date())

--------------

 

Query OK, 1 row affected (0.00 sec)

 

--------------

insert into andrews_table values (current_date)

--------------

 

Query OK, 1 row affected (0.00 sec)

 

--------------

select * from andrews_table

--------------

 

+------------+

| col1       |

+------------+

| 2012-01-13 |

| 2012-01-13 |

| 2012-01-13 |

| 2012-01-13 |

| 2012-01-13 |

| 2012-01-13 |

+------------+

6 rows in set (0.00 sec)

 

--------------

drop table andrews_table

--------------

 

Query OK, 0 rows affected (0.00 sec)

 

mysql>