primary key index column sort order

Questions and answers regarding general SQL and backend databases

Re: primary key index column sort order

Postby ROCLASI » Mon Sep 14, 2015 11:19 am

Again, 2 different perspectives. Relational database design vs OO design.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: primary key index column sort order

Postby ROCLASI » Mon Sep 14, 2015 2:36 pm

goldcougar wrote:The order of the columns in the SELECT doesn't matter. All the DB engines I've used (SQL Server, MySQL, Postgres) are smart enough to use your index regardless of order (as long as all the pk columns are part of the query), and regardless of how you created the index (like most selective column first). So Servoy issues the queries Alphabetical first, your database sees all the PK columns are in the SELECT, and will use your index, which hopefully you've created to be most optimal.
The only real performance issue would be if you also tried to use them in a different order in the ORDER BY portion of the query, then it wouldn't likely be able to use the index.


This is correct. And I think this also answers Robert Huber's original question.
I.e. just create the index how the database wants it, as long as Servoy is querying all the keys (in any order) this will work out fine. Using indexes for sorting does require the correct order.
Robert Ivens
SAN Developer / Servoy Valued Professional / Servoy Certified Developer

ROCLASI Software Solutions / JBS Group, Partner
Mastodon: @roclasi
--
ServoyForge - Building Open Source Software.
PostgreSQL - The world's most advanced open source database.
User avatar
ROCLASI
Servoy Expert
 
Posts: 5438
Joined: Thu Oct 02, 2003 9:49 am
Location: Netherlands/Belgium

Re: primary key index column sort order

Postby huber » Mon Sep 14, 2015 4:12 pm

Thanks, Scott and Robert for your feedbacks.

ROCLASI wrote:
goldcougar wrote:The order of the columns in the SELECT doesn't matter. All the DB engines I've used (SQL Server, MySQL, Postgres) are smart enough to use your index regardless of order (as long as all the pk columns are part of the query), and regardless of how you created the index (like most selective column first). So Servoy issues the queries Alphabetical first, your database sees all the PK columns are in the SELECT, and will use your index, which hopefully you've created to be most optimal.
The only real performance issue would be if you also tried to use them in a different order in the ORDER BY portion of the query, then it wouldn't likely be able to use the index.


This is correct. And I think this also answers Robert Huber's original question.
I.e. just create the index how the database wants it, as long as Servoy is querying all the keys (in any order) this will work out fine. Using indexes for sorting does require the correct order.


Best regards
Robert Huber
7r AG, Switzerland
SAN Developer
http://www.seven-r.ch
User avatar
huber
 
Posts: 516
Joined: Mon May 14, 2012 11:31 pm

Previous

Return to SQL Databases

Who is online

Users browsing this forum: No registered users and 9 guests