Indices

Your tblCar table should have two simple (i.e., single field) secondary indices. This is not the same thing as one composite (i.e., multi-field) index.

Consider these four secondary indices on the employee table:

CREATE INDEX idxA ON tblEmployee (JobTitle)

CREATE INDEX idxB ON tblEmployee (AnnualSalary DESC)

CREATE INDEX idxC ON tblEmployee (JobTitle, AnnualSalary DESC)

CREATE INDEX idxD ON tblEmployee (AnnualSalary DESC, JobTitle)

The indices link/sort the employees in different orders:

idxA Order             idxC Order
JonesAccountant$60,000             Hamilton   Accountant$75,000
DavisAccountant$39,500             JonesAccountant$60,000
Hamilton   Accountant$75,000             DavisAccountant$39,500
MorganDitch Digger   $22,500             MorganDitch Digger   $22,500
FranklinFireman$53,750             Kennedy   Fireman$87,500
Kennedy   Fireman$87,500             LeeFireman$75,000
LeeFireman$75,000             FranklinFireman$53,750
WilsonJanitor$22,500             WilsonJanitor$22,500
MoteProfessor    $325             MoteProfessor    $325
 
idxB Order             idxD Order
Kennedy   Fireman$87,500             Kennedy   Fireman$87,500
LeeFireman$75,000             Hamilton   Accountant$75,000
Hamilton   Accountant$75,000             LeeFireman$75,000
JonesAccountant$60,000             JonesAccountant$60,000
FranklinFireman$53,750             FranklinFireman$53,750
DavisAccountant$39,500             DavisAccountant$39,500
WilsonJanitor$22,500             MorganDitch Digger   $22,500
MorganDitch Digger   $22,500             WilsonJanitor$22,500
MoteProfessor    $325             MoteProfessor    $325

More FAQs