MySQL Forums
Forum List  »  Stored Procedures

Re: Function in order by clause problem
Posted by: Peter Brawley
Date: October 18, 2015 02:56PM

> Is it possible to use function in 'order by' clause ?

Though they block Order By index optimisations, deterministic built-in functions otherwise work OK in MySQL Order By clauses. Deterministic user functions, not so much, eg ...

drop function if exists f;
create function f() returns varchar(5) 
  reads sql data deterministic
  return right(customers.companyname,5);

explain select right(companyname,5) from customers order by f() limit 10;
+----+-------------+-----------+-------+---------------+-------------+---------+------+------+-------------+
| id | select_type | table     | type  | possible_keys | key         | key_len | ref  | rows | Extra       |
+----+-------------+-----------+-------+---------------+-------------+---------+------+------+-------------+
|  1 | SIMPLE      | customers | index | NULL          | CompanyName | 42      | NULL |   91 | Using index |
+----+-------------+-----------+-------+---------------+-------------+---------+------+------+-------------+

select right(companyname,5) from customers order by f() limit 10;
+----------------------+
| right(companyname,5) |
+----------------------+
| steRR                |
| lados                |
| uería                |
|  Horn                |
| rages                |
| bbköp                |
| essen                |
|  fils                |
| radas                |
| n app                |
+----------------------+

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Function in order by clause problem
832
October 18, 2015 02:56PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.