Creating an Index for Fast Query (Confused)

[gnome]

Hi,

I am confused with my superior how he understands Progress DB Index.

How I understand the logic of creating indexes is something like these.

Table: Person
Field1: FirstName
Field2: LastName
Index: NameIndex
Index

Table Name	Person
Fields	Firstname,Lastname,Age
Index	NameIndex
	Index Fields: Firstname,Lastname

What I believe is when I query the records of this table using only one field for filter (e.g. Firstname only), your query performance is slower than using both Fields in the Index (Firstname & Lastname) and this opposed to what my superior knows:

For him, using only 1 field for filter makes same performance with using both fields in Index.

I am confused because I have tested this before in a 100,000 - 200,000 records before.

Can someone enlighten me?


Thanks Guys

 

[rzr]

Try this on Sports database, where you have an Index named Country-Post on fields Country & Postal-Code.

[FONT=courier new]FOR EACH Customer NO-LOCK:
   WHERE Customer.Country = "US" :
END.
[/FONT]

Compile the above code and take a XREF output you will notice that:

[FONT=courier new]C:\temp\a.p C:\temp\a.p 1 COMPILE C:\temp\a.p[/FONT][FONT=courier new]
C:\temp\a.p C:\temp\a.p 1 CPINTERNAL ISO8859-1[/FONT][FONT=courier new]
C:\temp\a.p C:\temp\a.p 1 CPSTREAM ISO8859-1[/FONT][FONT=courier new]
C:\temp\a.p C:\temp\a.p 1 STRING "Customer" 8 NONE UNTRANSLATABLE [/FONT][FONT=courier new]
C:\temp\a.p C:\temp\a.p 1 ACCESS sports.Customer Country [/FONT][FONT=courier new]
C:\temp\a.p C:\temp\a.p 1 STRING "US" 2 NONE TRANSLATABLE [/FONT][FONT=courier new]
[COLOR=#ff0000][B]C:\temp\a.p C:\temp\a.p 1 SEARCH sports.Customer Country-Post[/B][/COLOR][/FONT][FONT=courier new][COLOR=#ff0000][/COLOR]C:\temp\a.p C:\temp\a.p 4 STRING "Country-Post" 12 NONE UNTRANSLATABLE[/FONT][FONT=courier new]
[/FONT]

You can also refer to the notes from below link on how Progress selects an Index:
http://www.fast4gl.com/downloads/monographs/selection/selection.html
http://www.allegroconsultants.com/downloads?func=select&id=3

 

[Rob Fitzpatrick]

The index selection and bracketing rules are also documented in the ABL Triggers and Indexes manual.

 

[gnome]

That's weird cause I have a different result. Anyhow, the performance of query should differ I think because I believe if this is the case, I could make one index for a table and create an index with all fields so that I may dont have to worry.

C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 1 COMPILE C:\tmp\sportsdb\src\checkindex.p
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 1 CPINTERNAL ISO8859-1
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 1 CPSTREAM ISO8859-1
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 1 STRING "Customer" 8 NONE UNTRANSLATABLE 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 1 ACCESS sports.Customer Country 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 1 STRING "US" 2 NONE TRANSLATABLE 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 1 SEARCH sports.Customer Country-Post
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 2 ACCESS sports.Customer Cust-Num 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 2 STRING ">>>>9" 5 NONE TRANSLATABLE  FORMAT 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 2 ACCESS sports.Customer Country 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 2 STRING "x(20)" 5 NONE TRANSLATABLE  FORMAT 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 2 ACCESS sports.Customer Name 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 2 STRING "x(20)" 5 NONE TRANSLATABLE  FORMAT 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 2 ACCESS sports.Customer Address 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 2 STRING "x(20)" 5 NONE TRANSLATABLE  FORMAT 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 2 ACCESS sports.Customer Address2 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 2 STRING "x(20)" 5 NONE TRANSLATABLE  FORMAT 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 2 ACCESS sports.Customer City 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 2 STRING "x(12)" 5 NONE TRANSLATABLE  FORMAT 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 2 ACCESS sports.Customer State 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 2 STRING "x(20)" 5 NONE TRANSLATABLE  FORMAT 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 2 ACCESS sports.Customer Postal-Code 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 2 STRING "x(10)" 5 NONE TRANSLATABLE  FORMAT 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 2 ACCESS sports.Customer Contact 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 2 STRING "x(20)" 5 NONE TRANSLATABLE  FORMAT 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 2 ACCESS sports.Customer Phone 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 2 STRING "x(20)" 5 NONE TRANSLATABLE  FORMAT 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 2 ACCESS sports.Customer Sales-Rep 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 2 STRING "X(4)" 4 NONE TRANSLATABLE  FORMAT 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 2 ACCESS sports.Customer Credit-Limit 
C:\tmp\sportsdb\src\checkindex.p  C:\tmp\sportsdb\src\checkindex.p 2 STRING  "->,>>>,>>9" 10 NONE TRANSLATABLE  FORMAT 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 2 ACCESS sports.Customer Balance 
C:\tmp\sportsdb\src\checkindex.p  C:\tmp\sportsdb\src\checkindex.p 2 STRING  "->,>>>,>>9.99" 13 NONE TRANSLATABLE  FORMAT 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 2 ACCESS sports.Customer Terms 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 2 STRING "x(20)" 5 NONE TRANSLATABLE  FORMAT 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 2 ACCESS sports.Customer Discount 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 2 STRING ">>9%" 4 NONE TRANSLATABLE  FORMAT 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 2 ACCESS sports.Customer Comments 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 2 STRING "x(60)" 5 NONE TRANSLATABLE  FORMAT 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 3 STRING "Cust-Num" 8 LEFT TRANSLATABLE 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 3 STRING "Country" 7 LEFT TRANSLATABLE 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 3 STRING "Name" 4 LEFT TRANSLATABLE 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 3 STRING "Address" 7 LEFT TRANSLATABLE 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 3 STRING "Address2" 8 LEFT TRANSLATABLE 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 3 STRING "City" 4 LEFT TRANSLATABLE 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 3 STRING "State" 5 LEFT TRANSLATABLE 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 3 STRING "Postal-Code" 11 LEFT TRANSLATABLE 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 3 STRING "Contact" 7 LEFT TRANSLATABLE 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 3 STRING "Phone" 5 LEFT TRANSLATABLE 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 3 STRING "Sales-Rep" 9 LEFT TRANSLATABLE 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 3 STRING "Credit-Limit" 12 LEFT TRANSLATABLE 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 3 STRING "Balance" 7 LEFT TRANSLATABLE 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 3 STRING "Terms" 5 LEFT TRANSLATABLE 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 3 STRING "Discount" 8 LEFT TRANSLATABLE 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 3 STRING "Comments" 8 LEFT TRANSLATABLE 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 3 STRING "Cust-Num" 8 NONE UNTRANSLATABLE 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 3 STRING "Country" 7 NONE UNTRANSLATABLE 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 3 STRING "Name" 4 NONE UNTRANSLATABLE 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 3 STRING "Address" 7 NONE UNTRANSLATABLE 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 3 STRING "Address2" 8 NONE UNTRANSLATABLE 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 3 STRING "City" 4 NONE UNTRANSLATABLE 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 3 STRING "State" 5 NONE UNTRANSLATABLE 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 3 STRING "Postal-Code" 11 NONE UNTRANSLATABLE 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 3 STRING "Contact" 7 NONE UNTRANSLATABLE 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 3 STRING "Phone" 5 NONE UNTRANSLATABLE 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 3 STRING "Sales-Rep" 9 NONE UNTRANSLATABLE 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 3 STRING "Credit-Limit" 12 NONE UNTRANSLATABLE 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 3 STRING "Balance" 7 NONE UNTRANSLATABLE 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 3 STRING "Terms" 5 NONE UNTRANSLATABLE 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 3 STRING "Discount" 8 NONE UNTRANSLATABLE 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 3 STRING "Comments" 8 NONE UNTRANSLATABLE 
C:\tmp\sportsdb\src\checkindex.p  C:\tmp\sportsdb\src\checkindex.p 3 STRING  "------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------"  300 NONE UNTRANSLATABLE 
C:\tmp\sportsdb\src\checkindex.p C:\tmp\sportsdb\src\checkindex.p 3 STRING "Country-Post" 12 NONE UNTRANSLATABLE

 

[Cringer]

No don't do that! That's lazy!!!! It won't do what you think!! Indexes should be well thought out. You should only have the indexes you need.

 

[gnome]

Ok, it makes sense to me now. Thanks to all of you guys.
Thanks for pointing me to this link rzr:

http://www.fast4gl.com/downloads/mon...selection.html

 

[TomBascom]

Ok, it makes sense now but it is still worth debunking this before someone thinks it is a good idea...

I believe if this is the case, I could make one index for a table and create an index with all fields so that I may dont have to worry.

The most important thing about index efficiency is the number of equality matches on leading components.

Having a field be part of an index is only helpful if all components of the index prior to that field are specified with equality matches.