| Subject | Re: [firebird-php] ORDER By RAND() executes very slow |
|---|---|
| Author | Helen Borrie |
| Post date | 2010-05-04T07:55:10Z |
At 04:24 PM 4/05/2010, you wrote:
If you want to order by anything, the whole set of values for the ordering criteria must be available *after* the set is complete.
If you insist on using SQL to do this thing, try working with a derived table row structure that contains a random value and use that derived field to sort on. It still won't be fast since non-indexed fields don't give the sorting engine any base for a fast sort. It does seem like a presentational issue: something the client side ought to be better equipped to do.
./hb
>Hello all,Well, in SQL terms, your query is illogical. ORDER BY is for ordering a set on a defined subset of real values that are present on every row (which might be the result of an expression applicable to every row).
>
>I have a simple firebird sql query as
>
>SELECT FIRST 1000 a."id", a."what_they_do", a."does", a."improves"
>FROM "inspections" a ORDER BY RAND() ASC;
>
>where "inspections" table having large no of records and containing text fields but by using ORDER BY RAND() it executes very slow
>
>please help ... how can i improve this query to get random records?
If you want to order by anything, the whole set of values for the ordering criteria must be available *after* the set is complete.
If you insist on using SQL to do this thing, try working with a derived table row structure that contains a random value and use that derived field to sort on. It still won't be fast since non-indexed fields don't give the sorting engine any base for a fast sort. It does seem like a presentational issue: something the client side ought to be better equipped to do.
./hb