How to execute static re-ordering of SQL database rows??

[IaPuP]

I have an application that retrieves large volumes of data from an SQL database that must be sorted by two different columns.

Since most of my queries are SELECT statements and *most* of them use this particular dual-sorted order, this adds quite a load on the server.

My question is this:

Is there a way to statically re-order a table by one or more entries so I do not have to dynamically have it re-ordered?
Is my only option to maintain separate tables (one created as an ordered set of the other) or can I physically modify the primary table to display in sorted order.

it might be important to mention that these tables DO have an AUTO-INCREMENT Key. I"m not sure if that places restrictions on the table or not.

I haven't been able to find much good info on this.

I will be using MySQL but my question applies to Postgre-SQL and other systems.


Thanks in advance!

Eric

 

[Jace]

In sql 7.0 you can create a clustered index on a column. Might be close to what you're looking for.

 

[noninterleaved]

If you are not going to be updating the tables in question very often, you could do a SELECT INTO to generate a new table with the sorted order, and then run your queries against that.

However... the catch there is that you are storing multiple copies of the data in your database, so there could be issues with data consistency. I would recommed this only if you are not going to be updating the "master" tables often.

If it is not incredibly time sensitive then you might want to consider having some script drop the tables every night, and then regenerate them... at least then your "live" data would be at most a day behind.

 

[noninterleaved]

I just reread your original post.

SQL is based off of relational algebra. RA does not specify the order in which tuples (rows) are returned. Because of this, you should never "count on" rows being ordered a specific way unless you specify some "order by" clause.

In practice, most rdbms return rows in some well-defined order, such as the order that they are actually written physically into the pages on disk. Sometimes this corresponds to insertion order, but it doesn't need to be.

So it sounds like you are trying to do something which is a RA violation... probably not too good of an idea. You could play some tricks but as soon as you migrate to a different rdbms or even upgrade the version of mySql you are planning on using you will be in a world of hurt.

 

[IaPuP]

ok..

I found it.



Let me point out that I wasn't really thinking when I said "I don't want to dynamically re-order it" because I do...

There is no way I can keep the table in the right order because of the rate of UPDATE and INSERT transactions, but I need the more common SELECT query to be *very fast*. I wanted something that would ocasionally re-order the table to make the sorting process simpler for the algorithm.

I guess I'm not sure the internal sorting algorithms of MySQL, so I shouldn't presume anything, but most algorithms (such as quicksort) like having ordered lists to sort from and will be much faster for it.

This may be MySQL specific, but here's the syntax to re-sort the table's default storage order.

ALTER TALBE table ORDER BY id;

I will probably throw it in after large sets of insert and update queries- but I'll definately look into the advantages of it since I bet it is quite expensive to execute juts any-old time. ;-)

Thanks,
Eric

 

[SQL]

Not too familiar with MySQL but check into indexing support as well with MySQL. A good index makes all the difference in the world with speed.

On a side note, a clustered index is usually used in situations that have a large number of rows with a column that has a relative few distinct values. For example, say the DMV has a table with hundreds of thousands of records for every registered driver in the state. A clustered index would go on the 'Make of car' field since there are a relative few makes (Ford, Chevy, Toyota, etc.) that exist several thousand times over in the column.

Of course then you run into issues with commit times, since indexing raises the time it takes to do UPDATES. No such thing as a free lunch.

 

[IaPuP]

I've thought about indexing, but there are a LOT of unique values here and I'm not sure how much it would gain me.

We'll see

Eric