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!

asked 21 Jun '15, 21:44

Davi%20in%20Wisconsin's gravatar image

Davi in Wisc...
868812
accept rate: 0%

edited 23 Jun '15, 03:26

Jens%20M%C3%BChlenhoff's gravatar image

Jens Mühlenhoff
1.3k51037


From Wikipedia (emphasis mine):

The order that the index definition defines the columns in is important. It is possible to retrieve a set of row identifiers using only the first indexed column. However, it is not possible or efficient (on most databases) to retrieve the set of row identifiers using only the second or greater indexed column.

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.

link

answered 23 Jun '15, 03:15

Jens%20M%C3%BChlenhoff's gravatar image

Jens Mühlenhoff
1.3k51037
accept rate: 18%

edited 23 Jun '15, 03:25

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.

link

answered 24 Jun '15, 07:12

Reinaldo's gravatar image

Reinaldo
816202346
accept rate: 6%

Your answer to the original question.
If responding to a request for additional information, please edit the question or use the comment functionality.
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "Title")
  • image?![alt text](/path/img.jpg "Title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×79
×25
×6

Asked: 21 Jun '15, 21:44

Seen: 1,825 times

Last updated: 24 Jun '15, 07:12

Advantage Developer Zone Contact Us Privacy Policy Copyright Info


Powered by Advantage Database Server and OSQA
Disclaimer: Opinions expressed here are those of the poster and do not necessarily reflect the views of the company.