Sorting rows in DB (by id or another field?)

What is the best way and why?

1. sorting rows by id
2. sorting rows by another field (create time for example)

Upd 1. Of course, I can add index for another field

Upd 2. Be开发者_如何学Pythonst for speed & usability

Do you mean sorting or indexing? Regardless of which database technology you are using, you can typically apply indexes on any column or on different combinations of columns. This allows the database engine to optimize query execution and make better execution plans. In a way, indexing is "sorting", for your database engine.

Actual sorting (as in ORDER BY MyColumn ASC|DESC) is really only relevant in the context of querying the database. How you decide to sort your query results would typically depend on how you intend to use your data.

I assume that you are using a relational database, such as MySQL or PostgreSQL, and not one of the non-SQL databases.

This means that you are interacting with the database using the SQL language, and the way you get data from the database is to use the SQL SELECT statement. In general, your tables will have a "key" attribute, which means that each row has a unique value for that attribute, and usually the database will store the data pre-sorted by that key (often in a B-tree data structure).

So, when you do a query, e.g. "SELECT firstname,lastname FROM employees;", where "employees" is a table with a few attributes, such as "employee_id", "firstname", "lastname", "home_address", and so forth, the data will generally be delivered in order of employee_id value.

To get the data sorted in a different order, you might make use of the SQL "ORDER_BY" clause in the SELECT statement. For example, if you wanted the data to be sorted by "lastname", then you might use something like "SELECT firstname,lastname FROM employees ORDER_BY lastname;".

One way that the database can implement this query is to retrieve all the data and then sort it before passing it on to the user.

In addition, it is possible to create indexes for the table which allows the database to find rows with particular values, or value ranges, for attributes or sets of attributes. If you have added a "WHERE" clause to the SELECT query which (dramatically) reduces the number of matching rows, then the database may use the index to speed up the query processing by first filtering the rows and then (if necessary) sorting them. Note that the whole topic of query optimization for databases is complex and takes into account a wide range of factors to try and estimate which of the possible query implementation alternatives will result in the fastest implementation.