MySql Interview Questions and Answers – Part 9
81 What are indexes? What are B-Trees?
Index makes your search faster. So defining indexes to your database will make your search faster.Most of the indexing fundamentals use “B-Tree” or “Balanced-Tree” principle. It’s not a principle that is something is created by SQL Server or ORACLE but is a mathematical derived fundamental.In order that “B-tree” fundamental work properly both of the sides should be balanced.
82 I have a table which has lot of inserts, is it a good database design to create indexes on that table? Insert’s are slower on tables which have indexes, justify it?or Why do page splitting happen?
All indexing fundamentals in database use “B-tree” fundamental. Now whenever there is new data inserted or deleted the tree tries to become unbalance.
Creates a new page to balance the tree.
Shuffle and move the data to pages.
So if your table is having heavy inserts that means it’s transactional, then you can visualize the amount of splits it will be doing. This will not only increase insert time but will also upset the end-user who is sitting on the screen. So when you forecast that a table has lot of inserts it’s not a good idea to create indexes.
83 What are the two types of indexes and explain them in detail? or What’s the difference between clustered and non-clustered indexes?
There are basically two types of indexes:-
In clustered index the non-leaf level actually points to the actual data.In Non-Clustered index the leaf nodes point to pointers (they are rowid’s) which then point to actual data.