2

I am really confused about the clustered and non clustered indexing and I have spent almost 5 hours today to clarify my doubt but I am still not able to find the answer of my following questions:

1- Clustered indexes are physically stored on table in indexed order.

Question: 1.A. What is the meaning of physically here? 1.B. Is a separate file created on system or the data is stored in table itself in sorted order?

2- Only one cluster index can be created on a table.

Question: 2.A. Does it mean when we create index on two column, it will automatically becomes a non clustered indexing?

3- MyISAM doesn't support clustered indexing but InnoDB support it

Question: 3.A. It means all the indexes created on MyISAM are by default non clustered indexes? 3.B. Is .MYI file contains all the non clustered indexes data in it? 4.B. Again if the index data is stored in a file in MyISAM then where InnoDB store their clustered index data?

Please help as I was asked these question in my interview today and I really not getting any clear answer about it?

Daniel Larsson
  • 6,278
  • 5
  • 44
  • 82
shruti angel
  • 31
  • 3
  • 1
    This isn't a homework answers website. – Incognito Nov 12 '13 at 13:50
  • putting this on hold with the reason that `problems with code you've written must describe the specific problem` seems slightly off because there is not really a problem with written code here, is there? However the problems/questions described seem to be specific and not too broad. – DrCopyPaste Nov 13 '13 at 10:06

1 Answers1

1

1 A) Physically means that it does not have to be computed from the data of a possibly much larger resultset to get the result you finally want, just saves some calculation time.

1 B) No. Separate files are only created if you enforce this.

2 A) No. First of all a clustered index CAN include multiple columns (but has to be unique unlike nonclustered ones) so it does not become nonclustered automatically, but implicitly. (By not including the UNIQUE keyword)

3 A) There are no clustered indexes in MyISAM, that said only nonclustered indexes are left.

3 B) Yes. .MYI is the index file type for MyISAM.

4 A) wasn't there was it? :)

4 B) In InnoDB, as I read it per default only one data file is created (that contains all databases, so also tables and their indexes are included), but you can also add new data files.

here are some links for further reading on indexes:

http://use-the-index-luke.com/welcome

https://dba.stackexchange.com/questions/1/what-are-the-main-differences-between-innodb-and-myisam

https://dba.stackexchange.com/questions/15531/why-does-innodb-store-all-databases-in-one-file

Community
  • 1
  • 1
DrCopyPaste
  • 4,023
  • 1
  • 22
  • 57