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.