Re: [PHPLIB] Please help! From: Kristian Koehntopp (kris <email protected>)
Date: 12/30/99

On Wed, Dec 29, 1999 at 10:51:14PM -0600, Chris Johnson wrote:
> $db->query("select 0 + user_id from members order by user_id desc");

This does not work. You are correctly converting user_id into a
numeric value by using it in a numeric context, but you are
sorting on the old user_id value, which still is a string and is
being handled as such.

Since you cannot have expressions in an "order by" clause, you
cannot actually write "order by user_id + 0" in MySQL. But you
can write expressions in your column list and name them and you
can refer to such columns by name in the "order by" clause.

Thus, you can

mysql> select user_id, user_id+0 as numeric_id from dummy order
by numeric_id de
sc;
+---------+------------+
| user_id | numeric_id |
+---------+------------+
| 1000 | 1000 |
| 5 | 5 |
| 4 | 4 |
| 3 | 3 |
| 2 | 2 |
| 1 | 1 |
+---------+------------+
6 rows in set (0.05 sec)

which will do what was requested. Compare this to

mysql> select user_id, user_id+0 as numeric_id from dummy order
by user_id desc;

+---------+------------+
| user_id | numeric_id |
+---------+------------+
| 5 | 5 |
| 4 | 4 |
| 3 | 3 |
| 2 | 2 |
| 1000 | 1000 |
| 1 | 1 |
+---------+------------+
6 rows in set (0.01 sec)

which sorts by string value instead. Also note the considerable
difference in execution time for even this small table. Not only
is MySQL very slow when it comes to type conversions, but using
a calculated column in an "order by" clause makes it impossible
to use any index as a sort accelerator in this case. For larger
table sizes this will be a very slow statement - even for small
values of "larger"!

A database which can handle indices on calculated values (such
as Oracle 8) will be much more efficient when processing this
statement. Alternatively one could consider to introduce a real
numeric_id column (with an index on it) and maintain a numeric
representation of the user_id value as an optimization. One
could

- run "update dummy set numeric_id = user_id+0" just before
  the select statement or use an optimized version of this
  involving a reference to "changed timestamp" column as
  part of a "where" clause of that update query to update
  only new rows.

- have the application maintain the numeric_id in parallel to
  the user_id column

- create a database trigger which maintains the numeric_id in
  parallel to the user_id (but not in MySQL).

Kristian

-
PHP3 Base Library Mailing List. Send messages to <phplib <email protected>>.
To unsubscribe, send "unsubscribe" to <phplib-request <email protected>> in
the body, not the subject, of your message.