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)

My SQL Table Names are Case Sensitive in UNIX

I looked at case sensitivity in MySQL database names in a couple of earlier posts. Type case sensitivity in the SEARCH THIS BLOG box to the right and click on Search to see them if you are interested. I recently discovered that table names are case sensitive on UNIX too: 

mysql> create table andrew (col1 varchar(10));

Query OK, 0 rows affected (0.03 sec)

 

mysql> select * from andrew;

Empty set (0.00 sec)

 

mysql> select * from ANDREW;

ERROR 1146 (42S02): Table 'test.ANDREW' doesn't exist

mysql>

How to Create a MySQL Table with a Primary Key

This is one way to create a table with a primary key. In this example, the primary key consists of one column. In a future post, I will show how to set up a primary key with more than one column: 

mysql> create table andrews_table

    -> (col1 varchar(10), primary key(col1));

Query OK, 0 rows affected (0.02 sec)

 

mysql> insert into andrews_table values ('Fred');

Query OK, 1 row affected (0.00 sec)

mysql>
  

The purpose of a primary key is to ensure that the values in the primary key column remain unique. So, if you try to insert Fred into the primary key column again, you would expect the insert to fail:

 

mysql> insert into andrews_table values ('Fred');

ERROR 1062 (23000): Duplicate entry 'Fred' for key 1

mysql> select * from andrews_table;

+------+

| col1 |

+------+

| Fred |

+------+

1 row in set (0.00 sec)

 

mysql>

MySQL create table ... like ...

If you want to create a table with the same description as another table, you can do this using like. In the example below, I create a table called andrews_table with 1 row of data. Then I use like to create a table called another_table with the same description. This table is empty to start with. To copy the data from andrews_table into another_table, I use a simple insert statement:

 

mysql> create table andrews_table

    -> (col1 varchar(10));

Query OK, 0 rows affected (0.03 sec)

 

mysql> insert into andrews_table values ('Fred');

Query OK, 1 row affected (0.12 sec)

 

mysql> create table another_table

    -> like andrews_table;

Query OK, 0 rows affected (0.00 sec)

 

mysql> desc another_table;

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

| Field | Type        | Null | Key | Default | Extra |

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

| col1  | varchar(10) | YES  |     | NULL    |       |

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

1 row in set (0.06 sec)

 

mysql> select * from another_table;

Empty set (0.00 sec)

 

mysql> insert into another_table

    -> (select * from andrews_table);

Query OK, 1 row affected (0.00 sec)

Records: 1  Duplicates: 0  Warnings: 0

 

mysql> select * from another_table;

+------+

| col1 |

+------+

| Fred |

+------+

1 row in set (0.00 sec)

 

mysql>