Having problem with MySQL programming

Asked By quinlan 0 points N/A Posted on - 11/08/2011

Hello there!

I would like to know about Index in detail like its need and types. I also need to know the number of Clustered Index that can be implemented in a table. Also, please enumerate the advantages and disadvantages of creating separate index for each column.

Please guide me…

Thank you…

• Status: Open
• Question Views: 781
• Answer Count: 2
• Vote Up 0 Vote Down

• Answer Accepted: Yes
• Question Category: MS SQL Server

Best Answer by Paul Mac

Go To Solution

Answered By ali_atif 0 points N/A #107491

Having problem with MySQL programming

1. The index has following types

– Clustered, Non-clustered, Unique, Index with included columns, Full-text, Spatial, Filtered, XML

 

– Clustered Index key is the base in sorting and storing the data in different tables. As clustered index is in B form tree and the retrieval of data is very fast due to clustered index keys values. it is like a telephone directory which arrange data as last name.

 

– Primary keys make clustered index itself automatically if no index is exist in the table. 

– Only one clustered index can be implemented in a single table if you want to implement more than one index than you have to use non-clustered index.

 

– Most important is that which is very simple, approaching transitive dependency which is the 3rd normal form we can achieve the cluster index concept because only primary key is deterring the other rest of non-keys in the table

 

Now talking about the separate indexing please keep following points in mind

 

– If you have more queries, and each of them uses a different column in the WHERE clause then its better to have multiple index of each column

 

– Query optimization, result is much faster with separate indexes

 

Disadvantages

 

– Minimizing the query speed sometimes as WHERE clause has to do much operation in finding the right column

 

– Much Disk Space required with separate index, may be reason to slow the server

 

Might This Will Help You

About ali_atif

Questions
0

Answers
3

Best Answers
0

• Vote Up 0 Vote Down

• Posted on - 11/17/2011
• Question Category: MS SQL Server

Best Answer

Best Answer

Answered By Paul Mac 0 points N/A #107493

Having problem with MySQL programming

Index:

Index is a data structure that improves the speed of the data retrieval operations on database.

Indexes are arranged in the form of B-trees where the leaf node holds the data. Since the data is in stored order indexes know which record is stored where so data can be retrieved quickly.

Types of Indexes:

1) Clustered Index.

2) Non Clustered Index.

Clustered Index:

A clustered index reorganizes the way the records in the tables are physically stored.

A table can have only one clustered index and the leaf nodes of clustered index contain data pages.

A clustered index will be created on a table by default when primary key of a table is created.

Non Clustered Index:

A non clustered index is a special type of index in which the logic order of the index does not match the physical store order on a row.

A non clustered index does not consist of data pages but pointer.

A non clustered index survives on its own.

Advantages:

1) Query optimization.

2) Uniqueness.

3) Text Searching.

Disadvantages:

1) Disk space.

2) Slowdown speed of writing queries.

About Paul Mac

Questions
0

Answers
42

Best Answers
26

• Vote Up 0 Vote Down

• Posted on - 01/05/2012
• Question Category: MS SQL Server