MySQL and database queries: sorting

Question

First of all, sorry for my english.

I have a C++ desktop application which gets rows from a database, and, for each row, the app creates an object which represent that row from that specific table. Each table has its corresponding class (I use ODB for that).

Once I've recovered the rows of a table, I show them in a table, which can be sorted by columns. Each column has a "sort" icon which allows to sort the table entries according to that column.

My question is, what is what quality apps usually do? Making other query each time the table must be sorted? or to sort the objects manually, using for example, a std::set? Which is faster?

I think sorting the entries using a std::set is faster because we avoid communication with the MySQL server, but at the same time perhaps the MySQL optimizer do some magic if we reorder multiple times the same database table, specially with index involved. I think that it could even depend on the frequency of these sort operations.

Anyway, I want to know pros and cons of both approaches.

Answer 1

Many applications let the database perform most of the work.

When tables are created, the application tells the database to set up columns for searching (indexing). The database will usually create an index table of . This makes searching faster because the order of the data in the table does not need to be sorted.

The applications would send the database a query statement to choose data from the database in a needed order. The application then iterates over the data.

When displaying data in a GUI grid, many frameworks perform the sorting for you. You tell the GUI which column to use for sorting and have the GUI resort and then display the data. Real applications use existing libraries and frameworks as much as possible.

If there is enough memory for your table, read the data in and sort the table. Otherwise, tell the database to generate a new view and reload the table in the GUI (as necessary).