Home >> FAQs/Tutorials >> MySQL Tutorials >> Index

MySQL FAQs - Understanding SQL CREATE, ALTER and DROP Statements

By: FYIcenter.com

Part:   1  2  3  4   5 

(Continued from previous part...)

How To Drop an Existing Table?

If you want to delete an existing table and its data rows, you can use the "DROP TABLE" statement as shown in the tutorial script below:

mysql> SELECT * FROM tipBackup;
+----+-------------+-------------------------+-------------+
| id | subject     | description             | create_date |
+----+-------------+-------------------------+-------------+
|  1 | Learn MySQL | Visit dev.fyicenter.com | 2006-07-01  |
+----+-------------+-------------------------+-------------+
1 row in set (0.40 sec)

mysql> DROP TABLE tipBackup;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM tipBackup;
ERROR 1146 (42S02): Table 'fyi.tipbackup' doesn't exist

Be careful, when you use the "DROP TABLE" statement. All data rows are gone too.

How To Create a Table Index?

If you have a table with a lots of rows, and you know that one of the columns will be used often as a search criteria, you can add an index for that column to improve the search performance. To add an index, you can use the "CREATE INDEX" statement as shown in the following script:

mysql> CREATE TABLE tip (id INTEGER PRIMARY KEY,
   subject VARCHAR(80) NOT NULL,
   description VARCHAR(256) NOT NULL,
   create_date DATE NULL);
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE INDEX tip_subject ON tip(subject);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

How To Get a List of Indexes of an Existing Table?

If you want to see the index you have just created for an existing table, you can use the "SHOW INDEX FROM tableName" command to get a list of all indexes in a given table. The tutorial script below shows you a nice example:

mysql> SHOW INDEX FROM TIP;
+------------+-------------+--------------+-------------+...
| Non_unique | Key_name    | Seq_in_index | Column_name |...
+------------+-------------+--------------+-------------+...
|          0 | PRIMARY     |            1 | id          |...
|          1 | tip_subject |            1 | subject     |...
+------------+-------------+--------------+-------------+...
2 rows in set (0.03 sec)

It's interesting to see that there is a default index for the primary key column.

How To Drop an Existing Index?

If you don't need an existing index any more, you should delete it with the "DROP INDEX indexName ON tableName" statement. Here is an example SQL script:

mysql> DROP INDEX tip_subject ON tip;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW INDEX FROM TIP;
+------------+-------------+--------------+-------------+...
| Non_unique | Key_name    | Seq_in_index | Column_name |...
+------------+-------------+--------------+-------------+...
|          0 | PRIMARY     |            1 | id          |...
+------------+-------------+--------------+-------------+...
1 row in set (0.00 sec)

(Continued on next part...)

Part:   1  2  3  4   5