where is the index physically located in MySQL database

Just wondering where is the index info physically located in MySQL database if I have indexed a table with ALTER TABLE contacts ADD KEY (columnName); ?

• it will stay with that table and add a new spacial column to it;

• stay separately from that table;

I think the #2 is the correct answer.

Can we index columns from more than one tables? how?

For the MyISAM engine, a table's indexes are stored in the .MYI file (in the data directory, along with the .MYD and .frm files for the table).

For InnoDB engine, the indexes are stored in the tablespace, along with the table. If innodb_file_per_table option is set, the indexes will be in the table's .ibd file along with the .frm file.

No, it's not possible to create an index that references more than one table. An index is specific to a table.

StackExchange Database Administrators Q#62900, answer score: 8