YOUR WEBSITE

•  Declare a NOT NULL indexed column if possible.   NULL requires additional server decisions   and can be processed more simply and faster.  •  Avoid over indexing; don't index a column just because it can be indexed. If a column is never  going to be referred to in comparisons, then there's no need to index it.  •  Every index created slows down table updates.  If a row is inserted, an entry must be added to  each of the table's indexes.  •  An index on a column that has very few distinct values is unlikely to do much good.  •  Choose unique and non-unique indexes appropriately.  The choice may be influenced by the data  type of a column and the level of distinctness.  •  Index a column prefix rather than the entire column. Shortening the length of the key values can  improve performance by reducing the amount of disk I/O needed.  •  Avoid creating multiple indexes that overlap (have the same initial columns). 

DROP TABLE removes one or more tables. All table data and the table definition are  removed, so  be careful with this statement!   DROP [TEMPORARY] TABLE [IF EXISTS] table1;  Use IF EXISTS to prevent an error from occurring for tables that do not exist. An error is generated for each non-existent table when using IF EXISTS. The TEMPORARY keyword has the following effects:               1.  The statement drops only TEMPORARY tables.               2.  The statement does not end an ongoing transaction Using TEMPORARY is a good way to ensure that non-TEMPORARY table is not deleted by accident. DROP TABLE examples:  DROP TABLE table1;  DROP TABLE IF EXISTS table1;  DROP TEMPORARY TABLE eu_countries_temp; 

To remove records from a table without removing the table itself, use the DELETE or TRUNCATE TABLE  statement. Either of the following statements completely empties the named table:   DELETE FROM t;  TRUNCATE TABLE t;  DELETE takes an optional WHERE clause that identifies which records to remove. This is useful when only  a given subset of records from a table need to be deleted. The following statement removes only those  records from t that have a status column value of 'expired', in order by the id column and output limited to  4 rows:   DELETE FROM t WHERE status = 'expired' ORDER BY id LIMIT 4;  TRUNCATE also will reset the metadata held within the format files of the table being truncated ( .frm )  such as the AUTO_INCREMENT starting value. 

Renaming a table changes neither a table's structure nor its contents. The following statement renames table t1 to t2: ALTER TABLE t1 RENAME TO t2; Another way to rename a table is by using the RENAME TABLE statement: RENAME TABLE t1 TO t2; RENAME TABLE has an advantage over ALTER TABLE in that it can perform multiple table renames in a  single operation. One use for this feature is to swap the names of two tables: RENAME TABLE t1 TO tmp, t2 TO t1, tmp TO t2; For TEMPORARY tables, RENAME TABLE does not work. The ALTER TABLE command must be used  instead.

The second way to alter a column definition is to use a CHANGE clause. The CHANGE clause enables  the modification of both the column's definition and its name. To use this clause, specify the CHANGE  keyword, followed by the column's existing name, its new name, and its new definition, in that order. Note  that this means the existing name must be specified twice to change only the column definition (and not the  name). For example, change the last_name column from CHAR(30) to CHAR(40) without renaming  the column as follows: ALTER TABLE head_of_state CHANGE last_name last_name CHAR(40) NOT  NULL; To change the name as well (for example, to Surname), provide the new name following the existing  name: ALTER TABLE head_of_state CHANGE last_name Surname CHAR(40) NOT NULL; To change a column at a specific position within a table row, use FIRST or AFTER col_name. The  default is to change the last column.

One method to alter a column definition is to use a  MODIFY  clause.  The name of the column to be  changed must be specified, followed by its new definition. Assume that the ID column's data type must be  changed from  INT to  BIGINT, to allow the table to accommodate larger identification numbers.  In  addition, the column needs to be changed to  UNSIGNED to disallow negative values. The following  statement accomplishes this task:  ALTER TABLE eu_countries MODIFY new_population BIGINT(12) NOT NULL; That ALTER TABLE statement changes the table structure as follows:  +----------------------+-------------------+-------+------+----------+--------+  | Field                    | Type                | Null  | Key  | Default | Extra |  +----------------------+-------------------+-------+------+----------+--------+  | name                   | char(52)          | NO   |         | NULL    |          |  | new_population | decimal(12,0) | NO   |         | NULL    |          

After creating a table, there might be a change of requirements that forces changes to the structure of the  table.  If that happens, the table's structure can be modified without having to re-create the table.  This  section addresses some of the methods available for modifying the table structure and data.  Add Columns  For example, to add a column named Id to the eu_countries table, the following statement can be issued:  ALTER TABLE eu_countries ADD Id INT(6) NOT NULL;  That ALTER TABLE/ADD statement changes the table structure as follows:  mysql> DESCRIBE eu_countries;  +----------------------+-------------------+------+-----+-----------+--------+  | Field                    | Type                | Null | Key | Default  | Extra |  +----------------------+-------------------+------+-----+-----------+--------+  | name                   | char(52)          | NO   |       | NULL    |           |  | new_population | decimal(12,1) | NO   |       | 0.0        |  

To drop a column, use a DROP clause that names the column to be removed: ALTER TABLE eu_countries DROP id; The above ALTER TABLE/DROP statement changes the table structure as follows: +----------------+---------------+------+-----+---------+-------+ | Field                 | Type                | Null   | Key | Default    | Extra   | +----------------+---------------+------+-----+---------+-------+ | name                 | char(52)          | NO   |          | NULL    |           | | new_population | decimal(12,1)  | NO   |          | 0.0         |           | +----------------+---------------+-----+------+---------+-------+

            It is sometimes useful to create tables for temporary use.  This can be done by using the  CREATE  TEMPORARY TABLE command rather than  CREATE TABLE. A temporary table differs from a non-  temporary table in the following ways:   •  It's visible only to the client (and connection) that created it and may be used only by that client  (and connection).  This means that different clients (even with different connections) can create  temporary tables that have the same name and no conflict occurs.   •  A temporary table exists only for the duration of the connection in which it was created.  The  server drops a temporary table automatically when the client connection ends if the client has not  already dropped it.  •  A temporary table may have the same name as a non-temporary table.  The non-temporary table  becomes hidden to the client that created the temporary table as long as the temporary table exists.   •  A temporary table can be renamed