MySQL - do indexes get dropped when table is dropped?

In MySQL do indexes get automatically deleted when th开发者_Python百科e table is dropped? Is it true for other databases as well like Postgres?

yes it is deleted automatically to check it you can do following

CREATE TABLE myTable (column1 INT);
CREATE INDEX index_on_mytable ON table1 (column1);

It will create a table named myTable and associate index index_on_mytable to it

now see the details as follow

SELECT COUNT(*) FROM information_schema.STATISTICS WHERE INDEX_NAME = 'index_on_myTable';

this will return 1 it means index exists

now drop the table

DROP TABLE myTable;

and the check again

SELECT COUNT(*) FROM information_schema.STATISTICS WHERE INDEX_NAME = 'index_on_myTable';

this time it will return 0 as index is deleted with the table.

Yes. Indexes in an RDBMS can't exist independently of the table to which they pertain.

All table data is removed; this includes indexes and the table definition.

Indexes can always be rebuilt given table data and definition remain intact, but not the other way around. It's not possible to restore anything meaningful from indexes without anything else.

I'm not aware of another database server that would leave the indexes but that doesn't mean there isn't one.