|
I have a table and 2 of the fields are the person's name and the class they are in. I need to have an index specified because I allow the user to search by name to look for s certain person, and I use FindNearest which requires an index. I allow the user to specify if they want the DBGrid sorted by Name, Class or Class, Name. If I have the indexname property set to "Name_Class", my SQL doesn't work "select * from table order by class, name" The Index is defined as Name;Class My question is - if the index is on name,class is it not possible to sort by class,name? Do I need to have a second index on Class,Name and switch the index before running my new query "select * from table order by class, name" ? I though since both fields were included in the index I would be able to 'order by' either field. Thanks! |
|
From Wikipedia (emphasis mine):
Which means (for your scenario) that if you want to order by "class, name" the index must be defined as "class;name" and if you want to order by "name, class" the index must be defined as "name;class". If you need both orderings to be covered by an index, you have to define two indexes. |
|
I think you will be better served by having two indexes based on a single field each -that is one based on "name" and the other on "class"- and let the ADS engine figure out how to use each index bitmap to find and order the results. Having two indexes with both fields each will make the index bag twice the size it needs to be and won't be any more efficient than having two indexes based on each field at least for set based transactions. |
