5

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); ?

  1. it will stay with that table and add a new spacial column to it;
  2. stay separately from that table;

I think the #2 is the correct answer.

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

8

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.

1
  • Is the View the same as the stored procedure regarding the location? I mean the View is also stored somewhere and will be activated when it is referred? – 5YrsLaterDBA Apr 9 '14 at 19:16
0

Using MySQL, all table information, including keys, are located in the INFORMATION_SCHEMA database.

For a more detailed answer, you might want to ask on Database Administrators.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.