MySQL DROP table,create index,drop INDEX

MySQL DROP table,create index,drop INDEX
DROP [temporary] TABLE [IF EXISTS] tbl_name [, Tbl_name,...]
[RESTRICT | CASCADE]
DROP table removes one or more tables. All data and table definitions have been removed, so be sure to use this command carefully!

In the MySQL 3.22 or newer version, you can use the keyword IF EXISTS to prevent errors when the table does not exist. In 4.1, when using the If EXISTS, you will get a note for all tables that do not exist. View Chapter 4.5.6.9 Show Warnings | ERRORS.

RESTRICT and CASCADE are allowed to be easier to transplant. At present, they do not have any effect.

Note: DROP TABLE will automatically commit the currently active transaction (unless you are using MySQL 4.1 and use the TEMPORARY keyword).

Option temporary is ignored in 4.0. In 4.1, the candidate works as follows:

Only temporary tables are removed.
Does not end a running transaction.
Access rights are not checked.
Using temporary is a good safe way to prevent you from accidentally removing a real table.

6.5.7 CREATE INDEX Syntax

CREATE [unique| Fulltext] INDEX index_name
On Tbl_name (col_name[(length)],...)
The CREATE INDEX syntax does nothing in the previous version of MySQL 3.22. In a 3.22 or later release, the CREATE index is mapped to an ALTER TABLE statement for indexing. View Chapter 6.5.4 ALTER TABLE syntax.

In general, you create all the indexes of a table when you create the table itself with the creation table. View Chapter 6.5.3 CREATE TABLE syntax. CREATE index allows you to add an index to an existing table.

(Col1,col2,...) Creates a multiple-column index for the column list in the format. The index value is connected by the given column value.

For CHAR and VARCHAR columns, using the col_name (length) syntax, you can create indexes with only one part of the column. (For BLOBs and TEXT columns, length is required.) The statement here shows that the first 10 characters of the Name column are used to create an index:

Mysql> CREATE INDEX part_of_name on customer (name (10));
Because most names are usually not the same as the first 10 characters, this index should not be slower than the index created with the entire name. Similarly, indexed files created with partial column values will be smaller, which can save a lot of disk space or speed up the INSERT operation!

Note that if you are using a MySQL 3.23.2 or newer version and are MyISAM table types, then you can create an index on a column that can have a NULL value and an index on a blob/text column.

For more information on how MySQL uses indexes, see chapters 5.4.3 how MySQL uses indexes.

Fulltext indexes can only index VARCHAR and TEXT columns, and can only be applied to MyISAM tables. The fulltext index can be used in MySQL 3.23.23 and newer versions. View Chapter 6.8 MySQL Full-text Search.

6.5.8 DROP INDEX Syntax

Drop index index_name on tbl_name
DROP Index removes an index named index_name from the table tbl_name. Do not do anything in the previous version of MySQL 3.22. In 3.22 or later versions, DROP index is mapped to an ALTER TABLE statement to remove the index