order by field with more than 10000 ids

Question

I need to do specific ordering with use of order by field.

select * from table order by field(id,3,4,1,2.......upto 10000 ids)

As the ordering required is not gettable from SQL then how much it affect as per performance and is it feasible to do?

Updates from the comments:

• Ordering depends on user and category IDs and can be anything the user wants.
• The ordering specification changes (about) daily.

So, we need a custom ordering that depends on the user and category and this ordering needs to change daily.

Answer 1

The easiest way would be to put your ordering in a separate table (called ordering_table in this example):

 id | position
----+----------
  1 | 11
  2 | 42
  3 | 23
 etc.

The above would mean "put an id of 1 at position 11, 2 at position 42, 3 at position 23, ...". Then you can join that ordering table in:

SELECT t.id, t.col1, t.col2
FROM some_table t
JOIN ordering_table o ON (t.id = o.id)
ORDER BY o.position

Where ordering_table is the table (as above) that defines your strange ordering. This approach simply represents your ordering function as a table (any function with a finite domain is, essentially, just a table after all).

This "ordering table" approach should work fine as long as the ordering table is complete.

If you only need this strange ordering in one place then you could merge the position column into your main table and add NOT NULL and UNIQUE constraints on that column to make sure you cover everything and have a consistent ordering.

Further commenting indicates that you want different orderings for different users and categories and that the ordering will change on a daily basis. You could make separate tables for each condition (which would lead to a combinatorial explosion) or, as Mikael Eriksson and ypercube suggest, add a couple more columns to the ordering table to hold the user and category:

CREATE TABLE ordering_table (
    thing_id    INT NOT NULL,
    position    INT NOT NULL,
    user_id     INT NOT NULL,
    category_id INT NOT NULL
);

The thing_id, user_id, and category_id would be foreign keys to their respective tables and you'd probably want to index all the columns in ordering_table but a couple minutes of looking at the query plans would be worthwhile to see if the indexes get used would be worthwhile. You could also make all four columns the primary key to avoid duplicates. Then, the lookup query would be something like this:

SELECT t.id, t.col1, t.col2
FROM some_table t
LEFT JOIN ordering_table o
     ON (t.id = o.thing_id AND o.user_id = $user AND o.category_id = $cat)
ORDER BY COALESCE(o.position, 99999)

Where $user and $cat are the user and category IDs (respectively). Note the change to a LEFT JOIN and the addition of COALESCE to allow for missing rows in ordering_table, these changes will push anything that doesn't have a specified position in the order to the bottom of the list rather than removing them from the results completely.