ansaurus

Question

Search result in presented in specific order?

Answer 1

+1 A:

ORDER BY CASE `id` WHEN 90 THEN 0
                   WHEN 4 THEN 1
                   WHEN 40 THEN 2
                   ELSE 3
         END CASE

OR

ORDER BY FIND_IN_SET(`id`, '90,4,40')

OR

ORDER BY FIELD(`id`, 90, 4, 40)

zerkms 2010-09-20 04:20:39

i want know it can cause slow ? if table contains very many fields

meotimdihia 2010-09-20 04:21:52

many fields? this clause can cause the performance degradation if you have a really **lot of rows** in the result, not the fields number

zerkms 2010-09-20 04:24:09

yes, i mistake rows with fieds

meotimdihia 2010-09-20 04:27:42

If it is **a lot** of rows in the result - then they will be sorted in-memory, so this can be an issue, depending on how many rows you have.

zerkms 2010-09-20 04:29:59

@zerkms: So no, because he's only selecting 3 rows from the table that need to be ordered anyways. At least I'd hope his `id` field is unique.

animuson 2010-09-20 04:43:31

@animuson: so? any of expressions above will be evaluated on the result set - this cannot be optimized. i did not say it is a slow process, but i did emphasized that this can be an issue **on a big bunch** of rows only.

zerkms 2010-09-20 04:46:02

@zerkms: I was merely pointing out that none of these options would cause any noticeable slowness on his data because he's only selecting three rows that get ordered. No matter how many rows he has in his table, MySQL will always find those 3 ID numbers via his WHERE statement and then order them.

animuson 2010-09-20 04:54:53

@animuson: again: i emphasized the fact that this can cause any degradation only if he have a lot of rows in the **result**. is that enough clear for you?

zerkms 2010-09-20 05:18:14

Answer 2

A:

SORRY , but one more question :

SELECT id FROM films WHERE id IN (90,4,40) ORDER BY FIELD(id, 90, 4, 40) is ok but truthfully

 for ( $i = 1; $i <10; $id ++ ){
     $IDarray[] = random();
 }


SELECT id FROM films WHERE id IN "(". implode(',',$IDarray). " ) ";

I want order by ID as above but ID is random, i want keep order random of ID

meotimdihia 2010-09-20 05:00:29

Don't do this - edit the question itself.

Jonathan Leffler 2010-09-20 05:30:03

And, obviously, before the last semi-colon, add: `ORDER BY FIELD "(id," . implode(',',$IDarray) . ")"`

Jonathan Leffler 2010-09-20 05:33:13

ansaurus

tags:

views:

answers:

Search result in presented in specific order?

related questions