Federated primary Key (Federated index) index Analysis in SQL Server (MYSQL)

Recently, some people have asked this question, has not been to delve into the joint index specific use of logic, consult multiple articles, and tested to draw some conclusions

Test environment: SQL Server R2

The test results are similar to the MySQL Federated Index query mechanism, and you can think of MySQL as the same principle

====================================================

Federated Index Concept: When a few fields in the system are often queried, and the amount of data is large, to reach millions, multiple fields can be indexed

Usage Rules:

1. Leftmost principle, according to the index field, from left to right, and (where field is important, from left to right )

2.Or does not use a federated index

The query field in the 3.where statement contains all indexed fields , regardless of the field order , and can be arbitrarily

4. When the amount of data is low , the index is generally not used, and the database mechanism automatically determines whether to use the index

=====================================================

Test scripts (partly drawing on other authors ' scripts):

/*Create a test data table*/Create Tablemytesttable (IDvarchar(Ten) not NULL, the parentvarchar( +) not NULL, Addtimedatetime default(getdate()), Intcolumnint default(Ten), Bitcolumnbit default(1))Go/*add a random string test data time-consuming minutes*/Declare @count int=3557643Declare @i int =0Declare @id varchar(Ten),@parent varchar( +) while(@i<@count)beginSelect @id=left(newid(),Ten)if(@i %  -=0)beginSelect @parent=left(newid(), +)EndInsertMytesttable (id,parent)Values(@id,@parent)Select @i=@i+1EndGo

/ x not built index query test x/
DECLARE @beginTime datetime =getdate()Declare @elapsedSecond int =0Select *  fromMytesttablewhereParent='F92D6A9D-4E9E-4980-8B46-8AD938CEDCB4'  andId='fd3687f4-1'Select @elapsedSecond=DATEDIFF(Microsecond,@beginTime,GETDATE())Print 'find data consumption microseconds when index is not established'Print @elapsedSecondSelect @beginTime=GETDATE()Select *  fromMytesttablewhereParent='f535c18f-bd48-4d45-88df-9653bb9b422d'Select @elapsedSecond=DATEDIFF(Microsecond,@beginTime,GETDATE())Print 'find second column data consumption microseconds when index is not established'Print @elapsedSecond

/*Build an index*/Alter TableMytesttableAdd constraintPk_id_parentPrimary Key(IDASC, the parentASC)/*query after an index is established*/Declare @beginTime datetime =getdate()Declare @elapsedSecond int =0Select *  fromMytesttablewhereParent='F92D6A9D-4E9E-4980-8B46-8AD938CEDCB4'  andId='fd3687f4-1'Select @elapsedSecond=DATEDIFF(Microsecond,@beginTime,GETDATE())Print 'find data consumption microseconds when index is established'Print @elapsedSecond Select @beginTime=GETDATE()Select *  fromMytesttablewhereParent='F92D6A9D-4E9E-4980-8B46-8AD938CEDCB4'Select @elapsedSecond=DATEDIFF(Microsecond,@beginTime,GETDATE())Print 'find the second column of data consumption microseconds after indexing'Print @elapsedSecond

/*Index Use test conclusion*/Select *  fromMytesttablewhereId='fd3687f4-1' --with indexSelect *  fromMytesttablewhereId='fd3687f4-1'  andParent='F92D6A9D-4E9E-4980-8B46-8AD938CEDCB4'  andIntcolumn>0  --with indexSelect *  fromMytesttablewhereId='fd3687f4-1'  andIntcolumn>0   andParent='F92D6A9D-4E9E-4980-8B46-8AD938CEDCB4'    --with indexSelect *  fromMytesttablewhereId='fd3687f4-1'  andIntcolumn>0 --with indexSelect *  fromMytesttablewhereParent='F92D6A9D-4E9E-4980-8B46-8AD938CEDCB4'  andId='fd3687f4-1'   --with indexSelect *  fromMytesttablewhereParent='F92D6A9D-4E9E-4980-8B46-8AD938CEDCB4'  andIntcolumn>0   --No indexSelect *  fromMytesttablewhereParent='F92D6A9D-4E9E-4980-8B46-8AD938CEDCB4' orId='fd3687f4-1'   --No index

If you have any questions welcome message exchange!

Federated primary Key (Federated index) index Analysis in SQL Server (MYSQL)