SQL Federated Index vs. single column index

SQL Federated index vs. single column indexTags: sqlwebobjectstatistics optimized disk2012-06-12 13:46 27992 People read Comments (1) favorite reports Classification:Database (94)

Transferred from: http://liqita.iteye.com/blog/1205544

Background: At present, the popularity of the web is too fast, many sites will be due to large traffic data and the server has a habitual panic, a query statement can only be applied to a certain network environment. Queries that are not optimized do not apply when large amounts of data are encountered.

Federated Index Usage Conclusion:

1): If the first column or all of the federated Indexes appear in the query condition, the Federated index can be used.

2): In the Condition column, as long as the conditions are joined together, in the case of this article:

Last_name= ' 1′and first_name= ' 1′

And

First_name= ' 1′and last_name= ' 1′

, both before and after, will use the on- Union index .

3): The first column of the Federated Index does not appear in the query condition, and the second column with the Federated Index , or the third column, does not take advantage of the Federated Index query.

Application of single column index conclusion:

1): The index query can be used wherever the index column appears in the Condition column.

The similarities between the two:

1): To use the index, must meet the SARG standard.

2): All in order to improve the query speed.

3): Requires additional system overhead, disk space.

Add: Stmttext information to be generated, precede the query statement with: SET STATISTICS profile on. You can run it to see if your query is reasonable, so that it is truly optimized.

This article aims to discuss the circumstances under which the index can be used.

Index: Creating an index can be divided into two types depending on the query business: Single-column index, Federated index . As the name implies, a single-column index is the creation of an index on a column of a table, which is a federated index on multiple columns.

Comparison of advantages and disadvantages:

1): Index occupies space: Single column index is relatively small.

2): Index creation time: Single column index is relatively short.

3): Impact of Index on Insert,update,delete program: Single column index to be relatively low.

4): In multi-conditional queries, the combined index is more efficient.

Index usage scope: A single column index can appear anywhere in the Where condition, and the Federated index needs to be written in a certain order.

The test software environment used in this article is as follows: SQL05

DEMO: Create a People table that contains the person ID, name. Create a clustered index on the person ID and create a union on first_name and Last_Name

Index.

CREATE TABLE person (ID int, last_name varchar (), first_name varchar (30))

Create unique clustered index person_id on person (ID)

Create index person_name on person (last_name, first_name)

In the example above, the clustered index is created on the ID, and the following query uses the clustered index.

where id=1

where id>1

where id<1

where ID between 1 and N

Where id like ' 1% '

where ID in (...)

Description: ID lists the position in the current condition does not necessarily require the first column, not affected by the location.

However, the following query method does not use the clustered index.

where person_id +1=n

where person_id like '%5′

where person_id like '%5% '

where person_id ABS (15)

The advantage of a Federated Index column over a single-column index is that it is more accurate than a single-column index for a multi-conditional query. Take the staff table above, if

To query a person's full name, only know that first_name is difficult to find the person's full name immediately, if you know first_name and last_name will be very easy to find.

The following describes the application of the index according to different conditions and output column order.

First case: – The condition and output columns are the same order as the index columns

Select Last_name,first_name from person where last_name= ' 1′and first_name= ' 1′

Stmttext

Index Seek (OBJECT: ([bdg_web_vaction].[ DBO]. [Person]. [Person_name]),

SEEK: ([bdg_web_vaction]. [dbo]. [Person]. [last_name]=[@1]

and [Bdg_web_vaction]. [dbo]. [Person]. [first_name]=[@2]) ORDERED FORWARD)

Results: Using Person_name Federated Index Lookup

Second case: – The Condition column is different from the index column order, but the output column is the same

Select Last_name,first_name from person where first_name= ' 1′and last_name= ' 1′

Stmttext

Index Seek (OBJECT: ([bdg_web_vaction].[ DBO]. [Person]. [Person_name]),

SEEK: ([bdg_web_vaction]. [dbo]. [Person]. [Last_name]=[@2] and [bdg_web_vaction].

[dbo]. [Person]. [first_name]=[@1]) ORDERED FORWARD)

Results: Using Person_name Federated Index Lookup

Third case: – The Condition column and the output column are not the same as the order of the indexed columns

Select First_name,last_name from person where first_name= ' 1′and last_name= ' 1′

Index Seek (OBJECT: ([bdg_web_vaction].[ DBO]. [Person]. [Person_name]),

SEEK: ([bdg_web_vaction]. [dbo]. [Person].

[Last_name]=[@2] and [bdg_web_vaction]. [dbo]. [Person]. [first_name]=[@1]) ORDERED FORWARD)

Results: Using Person_name Federated Index Lookup

Fourth case: – Conditions listed in first_name and last_name add another condition

SELECT ID, first_name,last_name from person where first_name= ' 1′and id=1 and Last_name= ' 1′

Clustered Index Seek (OBJECT: ([bdg_web_vaction].[ DBO]. [Person]. [person_id]),

SEEK: ([bdg_web_vaction]. [dbo]. [Person]. [Id]=convert_implicit (int,[@2],0)),

WHERE: ([bdg_web_vaction]. [dbo]. [Person]. [first_name]=[@1] and [bdg_web_vaction]. [dbo]. [Person]. [Las

Result: Cannot use Person_name Federated index Lookup

Fifth case:--separate first_name and last_name in the output column

SELECT first_name,id,last_name from person where first_name= ' 1 ' and last_name= ' 1 '

Index Seek (OBJECT: ([bdg_web_vaction].[ DBO]. [Person]. [Person_name]),

SEEK: ([bdg_web_vaction]. [dbo]. [Person].

[Last_name]=[@2] and [bdg_web_vaction]. [dbo]. [Person]. [first_name]=[@1])

ORDERED FORWARD)

Results: Using Person_name Federated Index Lookup

Sixth scenario: The condition column does not appear in the first column of the Federated index

SELECT first_name,id,last_name from person where first_name= ' 1′

SELECT first_name,last_name from person where first_name= ' 1′

SELECT last_name, first_name from person where first_name= ' 1′

Index Scan (OBJECT: ([bdg_web_vaction].[ DBO]. [Person]. [Person_name]),

WHERE: ([bdg_web_vaction]. [dbo]. [Person]. [first_name]=[@1]))

Result: The Person_name Federated index cannot be exploited.

Seventh case: – The first column of a Federated index appears in the Condition column

SELECT first_name,id,last_name from person where last_name= ' 1′

SELECT first_name,last_name from person where last_name= ' 1′

SELECT last_name, first_name from person where last_name= ' 1′

Index Seek (OBJECT: ([bdg_web_vaction].[ DBO]. [Person]. [Person_name]),

SEEK: ([bdg_web_vaction]. [dbo]. [Person]. [last_name]=[@1]) ORDERED FORWARD)

Results: Using Person_name Federated Index Lookup

Combined Index Usage Summary:

1): If the first column or all of the federated Indexes appear in the query condition, the Federated index can be used.

2): In the Condition column, as long as the conditions are joined together, in the case of this article:

Last_name= ' 1′and first_name= ' 1′

And

First_name= ' 1′and last_name= ' 1′

, both before and after, will use the on- Union index .

3): The first column of the Federated Index does not appear in the query condition, and the second column with the Federated Index , or the third column, does not take advantage of the Federated Index query.

Summary of application of single column index:

1): The index query can be used wherever the index column appears in the Condition column.

The similarities between the two:

1): To use the index, must meet the SARG standard.

2): All in order to improve the query speed.

3): Requires additional system overhead, disk space.

Add: Stmttext information to be generated, precede the query statement with: SET STATISTICS profile on. You can run it to see if your query is reasonable, so that it is truly optimized.

Summary: Even if an index is created on a table, it does not help if the query statement is not scientifically written (it does not conform to the SARG standard), and you want to refine the query based on the table index, and create the index if no suitable index is available .

SQL Federated Index vs. single column index