Re: multiple column sort MySQL

#1

Michael Joel wrote:

Quote:

I am using

$qryResult = mysql_query("ALTER TABLE MyTable ORDER BY County");

Alter table don't return any useful query results when using mysql_query(), at
best you get true/false.
The benefit of the above query is doubtful and don't apply to InnoDB, it's
more a theoretical benefit and may give you performance degrades if you make
these alter table calls before your selects.

Quote:

But I would like it sorted by multiple columns such as

$qryResult = mysql_query("ALTER TABLE MyTable ORDER BY County");
$qryResult = mysql_query("ALTER TABLE MyTable ORDER BY Town");
$qryResult = mysql_query("ALTER TABLE MyTable ORDER BY BusinessName");
$qryResult = mysql_query("ALTER TABLE MyTable ORDER BY Name");

Quote:

With County of course having precedence (so the table gets sorted by
county, sub sorted by town, then busines name, then name, etc. etc.)

SELECT Column1, Column2, Column3, .. FROM MyTable ORDER BY Country, Town,
BusinessName, Name

That would most likely be the fastest and best way to get your result in the
order you want it.

--

//Aho

#2

On Dec 27, 10:07*am, "J.O. Aho" <u... (AT) example (DOT) net> wrote:

Quote:

Michael Joel wrote:
I am using

$qryResult = mysql_query("ALTER TABLE MyTable ORDER BY County");

Alter table don't return any useful query results when using mysql_query(), at
best you get true/false.
The benefit of the above query is doubtful and don't apply to InnoDB, it's
more a theoretical benefit and may give you performance degrades if you make
these alter table calls before your selects.

But I would like it sorted by multiple columns such as

$qryResult = mysql_query("ALTER TABLE MyTable ORDER BY County");
$qryResult = mysql_query("ALTER TABLE MyTable ORDER BY Town");
$qryResult = mysql_query("ALTER TABLE MyTable ORDER BY BusinessName");
$qryResult = mysql_query("ALTER TABLE MyTable ORDER BY Name");
With County of course having precedence (so the table gets sorted by
county, sub sorted by town, then busines name, then name, etc. etc.)

SELECT Column1, Column2, Column3, .. FROM MyTable ORDER BY Country, Town,
BusinessName, Name

That would most likely be the fastest and best way to get your result in the
order you want it.

--

* *//Aho

I think he should read the docs on what ALTER TABLE ORDER BY actually
does... read on:

" ORDER BY enables you to create the new table with the rows in a
specific order. Note that the table does not remain in this order
after inserts and deletes. This option is useful primarily when you
know that you are mostly to query the rows in a certain order most of
the time. By using this option after major changes to the table, you
might be able to get higher performance. In some cases, it might make
sorting easier for MySQL if the table is in order by the column that
you want to order it by later.

ORDER BY syntax permits one or more column names to be specified for
sorting, each of which optionally can be followed by ASC or DESC to
indicate ascending or descending sort order, respectively. The default
is ascending order. Only column names are permitted as sort criteria;
arbitrary expressions are not permitted. This clause should be given
last after any other clauses.

ORDER BY does not make sense for InnoDB tables that contain a user-
defined clustered index (PRIMARY KEY or NOT NULL UNIQUE index). InnoDB
always orders table rows according to such an index if one is
present."

I would question the sanity of using the command at all...

#3

On Tue, 27 Dec 2011 17:59:12 -0800 (PST), onedbguru
<onedbguru (AT) yahoo (DOT) com> wrote:

Quote:

I think he should read the docs on what ALTER TABLE ORDER BY actually
does... read on:

" ORDER BY enables you to create the new table with the rows in a
specific order. Note that the table does not remain in this order
after inserts and deletes. This option is useful primarily when you
know that you are mostly to query the rows in a certain order most of
the time. By using this option after major changes to the table, you
might be able to get higher performance. In some cases, it might make
sorting easier for MySQL if the table is in order by the column that
you want to order it by later.

ORDER BY syntax permits one or more column names to be specified for
sorting, each of which optionally can be followed by ASC or DESC to
indicate ascending or descending sort order, respectively. The default
is ascending order. Only column names are permitted as sort criteria;
arbitrary expressions are not permitted. This clause should be given
last after any other clauses.

ORDER BY does not make sense for InnoDB tables that contain a user-
defined clustered index (PRIMARY KEY or NOT NULL UNIQUE index). InnoDB
always orders table rows according to such an index if one is
present."

I would question the sanity of using the command at all...

It is the same (in)sanity as CREATE INDEX: a possible perfomance
tuning option, a way to tell the engine what you know about the
usage patterns.
Nothing more, nothing less. Irrelevant for set manipulation / SQL.

(Note: I'm not talking about a UNIQUE constraint here, that's a
totally different story).

Best regards,
--
( Kees Nuyt
)
c[_]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: multiple column sort MySQL

comp.databases.mysql comp.databases.mysql

Re: multiple column sort MySQL - 12-27-2011 , 09:07 AM

Re: multiple column sort MySQL - 12-27-2011 , 07:59 PM

Re: multiple column sort MySQL - 12-28-2011 , 04:07 PM