Does dropping a MySQL table automatically drop that table's indices too? [duplicate]
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!
精彩评论