Does dropping a MySQL table automatically drop that table's indices too? [duplicate]

This question already has answers here: Does dropping a table in MySQL also dr开发者_运维百科op the indexes? (3 answers) Closed 8 years ago.

Does dropping a MySQL table automatically drop that table's indices too?

---

Yes. It drops the indexes. This can be verified:

CREATE TABLE table1 (foo INT);
CREATE INDEX ix_table1_foo ON table1 (foo);
CREATE INDEX ix_table1_foo ON table1 (foo); -- fails: index already exists.
DROP TABLE table1;
CREATE TABLE table1 (foo INT);
CREATE INDEX ix_table1_foo ON table1 (foo); -- succeeds: index does not exist.

You can also verify it by looking in the information schema:

CREATE TABLE table1 (foo INT);
CREATE INDEX ix_table1_foo ON table1 (foo);

SELECT COUNT(*)
FROM information_schema.STATISTICS
WHERE INDEX_NAME = 'ix_table1_foo';  -- returns 1

DROP TABLE table1;

SELECT COUNT(*)
FROM information_schema.STATISTICS
WHERE INDEX_NAME = 'ix_table1_foo';  -- returns 0

---

Yes. Indices are part of their owner table and are freed along with the row data when dropped.

(Foreign-key indices on other tables that refer to it will prevent the table being dropped.)

---

When a table gets dropped all data, indexes, and linked information will be deleted aswell. You can look at this as a cascading delete of a row, when you delete a row all the information that was linked to it will be deleted aswell (foreign keys etc)

---

Yes, it does. Without a table, theres no reason to keep the indexes.

You can confirm this by creating a MyISAM table, and looking in the data folder for tablename.MYI. once you drop the table, this file will be gone.

---

http://dev.mysql.com/doc/refman/5.1/en/drop-table.html

All table data and the table definition are removed, so be careful with this statement!