Q: I have an application that takes large volumes of data from a database guy should be sorted by two different columns.
Since most of my questions and statements are SELECT * most * of them use this dual-sorted that, this adds server.
My whole load on the question is:
Is there a way to re-statically to a table by one or more items, so I do not have to dynamically re-ordered?
Is my only option for separate tables (one to keep up as an ordered set of the other) if I can physically change the primary table to display in sorted order.
it perhaps important to note that these tables do an auto-increment key have. I m not sure if that puts constraints on the table or not.
I failed to find much good info on this.
I will use Myguy but my question applies to postgres-man and other systems.
Thanks in advance!
Eric
Re: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
Re:Not too familiar with Myguy but check into indexing support as well with Myguy. 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. 
Re: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 Myguy, 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 Myguy 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
Re:I just reread your original post.
guy 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.
Re: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.
Re:In sql 7.0 you can create a clustered index on a column. Might be close to what you're looking for.
0 Comments.