Keeping data sorted in a table

[binary-nomad]

Hello,
To followup my last post, how do I keep a field in a table already
sorted. eg. if a field in it going to have values like "10", "3330" and
"1", I want the row with the "1' first in the table, and the "10"
second and so on. I want this to happen when I UPDATE, *not* when
SELECT-ing, as, according to the last post, if I 'm doing a SELECT num
where (num>1 AND num<2000) AND bar = "Toyota", by the time it gets past
the "10" row, it should know not to go any further down the list
looking for bar="Toyota".
Thanks.

[Aggro]

bi**********@hotmail.com wrote:

To followup my last post, how do I keep a field in a table already
sorted.
Databases don't have actual order for rows. It is only guaranteed that
the data will be sorted correctly when you run select with "order". If
you don't use "order", the data is _usually_ in the order you inserted
it in, but I wouldn't trust on that:

select * from tablename order by columnname;

So if you don't have any column you can sort the data in your
select-queries, you need to add one and for example insert numbers in
that column in the order you wish the rows to be.
it should know not to go any further down the list
looking for bar="Toyota"

MySQL database is propably smarter than you or I are. If you use indexes
properly, the database will automatically try to make queries as fast as
possible, so you don't need to worry about things like that. Sometimes
the fastest way could be to search for Toyota, instead of checking 2000
rows that have correct num-value. Assuming "bar" field has index also.

Or do you want your queries to run as you wish, no matter how slow it
will get?

[Gordon Burditt]

>To followup my last post, how do I keep a field in a table already

sorted.
You DON'T. TABLES DO NOT HAVE AN ORDER. They can have indexes.
Those are sorted. You should create indexes on a table considering
the uniqueness constraints you want, and the queries you intend to
make. Indexes, if done well, speed up queries a lot but slow down
inserts and deletes (and updates, if the keys change), so don't
just make an index on everything or every combination of everything.
eg. if a field in it going to have values like "10", "3330" and
"1", I want the row with the "1' first in the table, and the "10"
second and so on.
Tough noogies. TABLES DO NOT HAVE AN ORDER. However, you might
want to create an index on that field.
I want this to happen when I UPDATE, *not* when
SELECT-ing, as, according to the last post, if I 'm doing a SELECT num
where (num>1 AND num<2000) AND bar = "Toyota", by the time it gets past
the "10" row, it should know not to go any further down the list
looking for bar="Toyota".

Note that inserting a record physically at the beginning of a 10GB
database can be VERY time-consuming. Inserting it somewhere and
putting it an an index (or several of them) is much faster.

If you have indexes on both num and bar, MySQL can decide (probably
better than you can) whether it is best to do an exact match on bar
= 'Toyota' (then look for records with num within the proper range)
or do a range match on num (then look for records with bar =
'Toyota').

Or, if you have an index on (bar,num), it can find the exact records
required from the index. If you have an index on (num,bar), though,
that's not better than an index on num alone. Think about trying
to look up people in a phone book with the first name 'George' and
a last name beginning with M-Z.

Gordon L. Burditt