Hi,

Is that allowed to have identical values in a column which is declared
as "primary key". Is that true that rows of the table will be ordered
by values of elements of the primary-key-column (in the sense that the
MySQL server does not need order rows each time when the select
command is used)?

What should I do if I want to have my rows be ordered with respect a
column which may have identical values?

Thank you.

On Tue, 05 Feb 2008 02:30:12 +0100, Kurda Yon <kurdayon@yahoo.com> wrote:
> Is that allowed to have identical values in a column which is declared
> as "primary key".

No.

> Is that true that rows of the table will be ordered
> by values of elements of the primary-key-column (in the sense that the
> MySQL server does not need order rows each time when the select
> command is used)?

No.

ALTER TABLE tablename ORDER BY fieldname
<http://dev.mysql.com/doc/refman/5.0/en/alter-table.html>
However:
"Note that the table does not remain in this order after inserts and
deletes.

Also take a look at:
<http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html>

> What should I do if I want to have my rows be ordered with respect a
> column which may have identical values?

Just order by that column, and as long as you don't specify a second
column, or even all values are equal, the order of equals is
unpredictable, but should logically not matter.
--
Rik Wasmus

>
> ALTER TABLE tablename ORDER BY fieldname
> <http://dev.mysql.com/doc/refman/5.0/en/alter-table.html>
> However:
> "Note that the table does not remain in this order after inserts and
> deletes.

I think it is exactly what I need. I will reorder the table each time
when I delete or insert a new line (it happens not so often). By the
way, if I "select" elements from the table without usage of the "order
by", will the row of the table be given in the order which was
specified by "alter table ... order by ..."?

Kurda Yon wrote:
>> ALTER TABLE tablename ORDER BY fieldname
>> <http://dev.mysql.com/doc/refman/5.0/en/alter-table.html>
>> However:
>> "Note that the table does not remain in this order after inserts and
>> deletes.
>
> I think it is exactly what I need. I will reorder the table each time
> when I delete or insert a new line (it happens not so often). By the
> way, if I "select" elements from the table without usage of the "order
> by", will the row of the table be given in the order which was
> specified by "alter table ... order by ..."?
>

No guarantee, but generally, yes.

The only way to ensure order is to use the ORDER BY clause. What's your
reservation about using it, anyway?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Feb 4, 9:03 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> Kurda Yon wrote:
> >> ALTER TABLE tablename ORDER BY fieldname
> >> <http://dev.mysql.com/doc/refman/5.0/en/alter-table.html>
> >> However:
> >> "Note that the table does not remain in this order after inserts and
> >> deletes.
>
> > I think it is exactly what I need. I will reorder the table each time
> > when I delete or insert a new line (it happens not so often). By the
> > way, if I "select" elements from the table without usage of the "order
> > by", will the row of the table be given in the order which was
> > specified by "alter table ... order by ..."?
>
> No guarantee, but generally, yes.
>
> The only way to ensure order is to use the ORDER BY clause.
But, may be, it means, that I lose all advantages created by "alter
table ... order by ..."? Does the time needed for the ordering depend
on the "level" of ordering of the table? Can it be that the MySQL
server will try to order the ordered list and it will take as much
time as the ordering of an unordered list?

> What's your
> reservation about using it, anyway?
My English is not so good. I did not understand the question. If you
reformulate it, I probably will be able to answer it.

Kurda Yon wrote:
> On Feb 4, 9:03 pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>> Kurda Yon wrote:
>>>> ALTER TABLE tablename ORDER BY fieldname
>>>> <http://dev.mysql.com/doc/refman/5.0/en/alter-table.html>
>>>> However:
>>>> "Note that the table does not remain in this order after inserts and
>>>> deletes.
>>> I think it is exactly what I need. I will reorder the table each time
>>> when I delete or insert a new line (it happens not so often). By the
>>> way, if I "select" elements from the table without usage of the "order
>>> by", will the row of the table be given in the order which was
>>> specified by "alter table ... order by ..."?
>> No guarantee, but generally, yes.
>>
>> The only way to ensure order is to use the ORDER BY clause.
> But, may be, it means, that I lose all advantages created by "alter
> table ... order by ..."? Does the time needed for the ordering depend
> on the "level" of ordering of the table? Can it be that the MySQL
> server will try to order the ordered list and it will take as much
> time as the ordering of an unordered list?
>

Unless you're returning huge numbers of rows, the time to sort will not
be significant. And if you're looking at 10M+ rows in a table, you can
still optimize quickly by tuning the MySQL parameters.

>> What's your
>> reservation about using it, anyway?
> My English is not so good. I did not understand the question. If you
> reformulate it, I probably will be able to answer it.
>

Why don't you want to use ORDER BY in your SELECT statements? That's
the way to do it, and databases are optimized to sort quickly.

I suspect what you are doing is known as "premature optimization" - that
is, you're trying to optimize your code before you even know you have a
problem.

Wait until you have performance problems, then look for optimization
possibilities. You will find a lot of things you can do to speed things
up before you start worrying about ORDER BY.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On 5 Feb, 01:30, Kurda Yon <kurda...@yahoo.com> wrote:
> Hi,
>
> Is that allowed to have identical values in a column which is declared
> as "primary key". Is that true that rows of the table will be ordered
> by values of elements of the primary-key-column (in the sense that the
> MySQL server does not need order rows each time when the select
> command is used)?
>
> What should I do if I want to have my rows be ordered with respect a
> column which may have identical values?
>
> Thank you.

Having followed the conversation in this thread, I would advise you to
create a non-unique index on the column that contains the value that
you want to use in the ORDER BY. Then MySQL will use that index to
retrieve the rows in that order (as long as you code the ORDER BY
clause), if it decides that that is the most efficient method of
accomplishing the task.

>
> Having followed the conversation in this thread, I would advise you to
> create a non-unique index on the column that contains the value that
> you want to use in the ORDER BY. Then MySQL will use that index to
> retrieve the rows in that order (as long as you code the ORDER BY
> clause), if it decides that that is the most efficient method of
> accomplishing the task.

I do the following:
CREATE TABLE testtmp2 (id bigint(20), priority FLOAT(11,4), INDEX
(priority));
insert into testtmp2 (id,priority) values(17,1000.12134);
insert into testtmp2 (id,priority) values(18,1001.12134);
insert into testtmp2 (id,priority) values(19,999.12134);
select * from testtmp2;

And I get:
+-----------+-----------+
| id | priority |
+-----------+-----------+
| 17 | 1000.1213 |
| 18 | 1001.1213 |
| 19 | 999.1213 |
+-----------+-----------+

So, the rows are not ordered by priority. Should I use "order by
priority" when I "select"? (Is that what you mean by "as long as you
code the ORDER BY clause"?). Does the usage of the INDEX release the
MySQL server from the doing the ordering by the INDEX (when it is
required in the "select"-command?).

Kurda Yon wrote:
>> Having followed the conversation in this thread, I would advise you to
>> create a non-unique index on the column that contains the value that
>> you want to use in the ORDER BY. Then MySQL will use that index to
>> retrieve the rows in that order (as long as you code the ORDER BY
>> clause), if it decides that that is the most efficient method of
>> accomplishing the task.
>
> I do the following:
> CREATE TABLE testtmp2 (id bigint(20), priority FLOAT(11,4), INDEX
> (priority));
> insert into testtmp2 (id,priority) values(17,1000.12134);
> insert into testtmp2 (id,priority) values(18,1001.12134);
> insert into testtmp2 (id,priority) values(19,999.12134);
> select * from testtmp2;
>
> And I get:
> +-----------+-----------+
> | id | priority |
> +-----------+-----------+
> | 17 | 1000.1213 |
> | 18 | 1001.1213 |
> | 19 | 999.1213 |
> +-----------+-----------+
>
> So, the rows are not ordered by priority. Should I use "order by
> priority" when I "select"? (Is that what you mean by "as long as you
> code the ORDER BY clause"?). Does the usage of the INDEX release the
> MySQL server from the doing the ordering by the INDEX (when it is
> required in the "select"-command?).

SELECT * FROM testtmp2 ORDER BY priority ASC, id ASC;

....will first order the results on the 'priority' column then on the 'id
' column. The final result set would be something like:

+-----------+-----------+
| id | priority |
+-----------+-----------+
| 19 | 999.1213 |
| 17 | 1000.1213 |
| 20 | 1000.1213 |
| 21 | 1000.1213 |
| 18 | 1001.1213 |
+-----------+-----------+



--
Norman
Registered Linux user #461062

On Feb 6, 11:43 pm, Norman Peelman <npeel...@cfl.rr.com> wrote:
> Kurda Yon wrote:
> >> Having followed the conversation in this thread, I would advise you to
> >> create a non-unique index on the column that contains the value that
> >> you want to use in the ORDER BY. Then MySQL will use that index to
> >> retrieve the rows in that order (as long as you code the ORDER BY
> >> clause), if it decides that that is the most efficient method of
> >> accomplishing the task.
>
> > I do the following:
> > CREATE TABLE testtmp2 (id bigint(20), priority FLOAT(11,4), INDEX
> > (priority));
> > insert into testtmp2 (id,priority) values(17,1000.12134);
> > insert into testtmp2 (id,priority) values(18,1001.12134);
> > insert into testtmp2 (id,priority) values(19,999.12134);
> > select * from testtmp2;
>
> > And I get:
> > +-----------+-----------+
> > | id | priority |
> > +-----------+-----------+
> > | 17 | 1000.1213 |
> > | 18 | 1001.1213 |
> > | 19 | 999.1213 |
> > +-----------+-----------+
>
> > So, the rows are not ordered by priority. Should I use "order by
> > priority" when I "select"? (Is that what you mean by "as long as you
> > code the ORDER BY clause"?). Does the usage of the INDEX release the
> > MySQL server from the doing the ordering by the INDEX (when it is
> > required in the "select"-command?).
>
> SELECT * FROM testtmp2 ORDER BY priority ASC, id ASC;
>
> ...will first order the results on the 'priority' column then on the 'id
> ' column. The final result set would be something like:
>
> +-----------+-----------+
> | id | priority |
> +-----------+-----------+
> | 19 | 999.1213 |
> | 17 | 1000.1213 |
> | 20 | 1000.1213 |
> | 21 | 1000.1213 |
> | 18 | 1001.1213 |
> +-----------+-----------+
>
> --
> Norman
> Registered Linux user #461062

Are the table rows ordered with respect to the index before I use
"select"? I mean, does MySQL server spend some time to order elements
in your example?

Kurda Yon wrote:
> On Feb 6, 11:43 pm, Norman Peelman <npeel...@cfl.rr.com> wrote:
>> Kurda Yon wrote:
>>>> Having followed the conversation in this thread, I would advise you to
>>>> create a non-unique index on the column that contains the value that
>>>> you want to use in the ORDER BY. Then MySQL will use that index to
>>>> retrieve the rows in that order (as long as you code the ORDER BY
>>>> clause), if it decides that that is the most efficient method of
>>>> accomplishing the task.
>>> I do the following:
>>> CREATE TABLE testtmp2 (id bigint(20), priority FLOAT(11,4), INDEX
>>> (priority));
>>> insert into testtmp2 (id,priority) values(17,1000.12134);
>>> insert into testtmp2 (id,priority) values(18,1001.12134);
>>> insert into testtmp2 (id,priority) values(19,999.12134);
>>> select * from testtmp2;
>>> And I get:
>>> +-----------+-----------+
>>> | id | priority |
>>> +-----------+-----------+
>>> | 17 | 1000.1213 |
>>> | 18 | 1001.1213 |
>>> | 19 | 999.1213 |
>>> +-----------+-----------+
>>> So, the rows are not ordered by priority. Should I use "order by
>>> priority" when I "select"? (Is that what you mean by "as long as you
>>> code the ORDER BY clause"?). Does the usage of the INDEX release the
>>> MySQL server from the doing the ordering by the INDEX (when it is
>>> required in the "select"-command?).
>> SELECT * FROM testtmp2 ORDER BY priority ASC, id ASC;
>>
>> ...will first order the results on the 'priority' column then on the 'id
>> ' column. The final result set would be something like:
>>
>> +-----------+-----------+
>> | id | priority |
>> +-----------+-----------+
>> | 19 | 999.1213 |
>> | 17 | 1000.1213 |
>> | 20 | 1000.1213 |
>> | 21 | 1000.1213 |
>> | 18 | 1001.1213 |
>> +-----------+-----------+
>>
>> --
>> Norman
>> Registered Linux user #461062
>
> Are the table rows ordered with respect to the index before I use
> "select"? I mean, does MySQL server spend some time to order elements
> in your example?
>

The elements are retrieved according to the order you specify. An index
may or may not be used, based on a lot of factors.

Indexes are used to speed up access to the data, not for sorting.
However, in a case like above, MySQL will *probably* use the index to
gather the data.

But you shouldn't have to worry about sorting time unless you're
returning a huge number of rows. The sort algorithm is very efficient
because it is used so often.

Don't worry about your performance until you *see* a problem!

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Feb 7, 11:47 am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
> Kurda Yon wrote:
> > On Feb 6, 11:43 pm, Norman Peelman <npeel...@cfl.rr.com> wrote:
> >> Kurda Yon wrote:
> >>>> Having followed the conversation in this thread, I would advise you to
> >>>> create a non-unique index on the column that contains the value that
> >>>> you want to use in the ORDER BY. Then MySQL will use that index to
> >>>> retrieve the rows in that order (as long as you code the ORDER BY
> >>>> clause), if it decides that that is the most efficient method of
> >>>> accomplishing the task.
> >>> I do the following:
> >>> CREATE TABLE testtmp2 (id bigint(20), priority FLOAT(11,4), INDEX
> >>> (priority));
> >>> insert into testtmp2 (id,priority) values(17,1000.12134);
> >>> insert into testtmp2 (id,priority) values(18,1001.12134);
> >>> insert into testtmp2 (id,priority) values(19,999.12134);
> >>> select * from testtmp2;
> >>> And I get:
> >>> +-----------+-----------+
> >>> | id | priority |
> >>> +-----------+-----------+
> >>> | 17 | 1000.1213 |
> >>> | 18 | 1001.1213 |
> >>> | 19 | 999.1213 |
> >>> +-----------+-----------+
> >>> So, the rows are not ordered by priority. Should I use "order by
> >>> priority" when I "select"? (Is that what you mean by "as long as you
> >>> code the ORDER BY clause"?). Does the usage of the INDEX release the
> >>> MySQL server from the doing the ordering by the INDEX (when it is
> >>> required in the "select"-command?).
> >> SELECT * FROM testtmp2 ORDER BY priority ASC, id ASC;
>
> >> ...will first order the results on the 'priority' column then on the 'id
> >> ' column. The final result set would be something like:
>
> >> +-----------+-----------+
> >> | id | priority |
> >> +-----------+-----------+
> >> | 19 | 999.1213 |
> >> | 17 | 1000.1213 |
> >> | 20 | 1000.1213 |
> >> | 21 | 1000.1213 |
> >> | 18 | 1001.1213 |
> >> +-----------+-----------+
>
> >> --
> >> Norman
> >> Registered Linux user #461062
>
> > Are the table rows ordered with respect to the index before I use
> > "select"? I mean, does MySQL server spend some time to order elements
> > in your example?
>
> The elements are retrieved according to the order you specify. An index
> may or may not be used, based on a lot of factors.
>
> Indexes are used to speed up access to the data, not for sorting.
> However, in a case like above, MySQL will *probably* use the index to
> gather the data.
>
> But you shouldn't have to worry about sorting time unless you're
> returning a huge number of rows.

The table will contain up to the 100 000 rows. And time is critical.
If it orders 1 second, it should be already a problem for me.

Kurda Yon wrote:
>
> The table will contain up to the 100 000 rows. And time is critical.
> If it orders 1 second, it should be already a problem for me.
>

100K rows is a pretty small table. Now if you were talking 100M rows,
you might have some performance considerations.

As I said - don't prematurely optimize. If you have a performance
problem, *then* is the time to look at the cause and fix it.

Otherwise you're just wasting your time and our time for something
that's not going to happen 99% of the time. And the other 1% it won't
be due to the presence of an ORDER BY clause.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

On Thu, 7 Feb 2008 18:47:43 -0800 (PST), Kurda Yon wrote:
> On Feb 7, 11:47 am, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>> Kurda Yon wrote:
>> > Are the table rows ordered with respect to the index before I use
>> > "select"? I mean, does MySQL server spend some time to order elements
>> > in your example?
>>
>> The elements are retrieved according to the order you specify. An index
>> may or may not be used, based on a lot of factors.
>>
>> Indexes are used to speed up access to the data, not for sorting.
>> However, in a case like above, MySQL will *probably* use the index to
>> gather the data.
>>
>> But you shouldn't have to worry about sorting time unless you're
>> returning a huge number of rows.
>
> The table will contain up to the 100 000 rows. And time is critical.
> If it orders 1 second, it should be already a problem for me.

Is that the size of your input table or your result set? Because the
sorting time that you're concerning yourself about is the time to sort
the OUTPUT. (There's some complications to this, of course, but it's
true as far as the statement goes.)

--
53. If the beautiful princess that I capture says "I'll never marry you!
Never, do you hear me, NEVER!!!", I will say "Oh well" and kill her.
--Peter Anspach's list of things to do as an Evil Overlord