Should I store data pre-ordered rather than ordering on the fly?

I'm using MySQL and I'm wondering if it's a good strategy to presort my data so when a user accessed the information, it's not having to sort it on the fly?

Basically, I have an HTML table with is being populated with paginated data from the database, this is ordered by a particular column and can sometimes be a little sluggish - I was thinking about reordering the table on a nightly basis so the order by can be removed from the query.

Is this general practice or should I avoid this?

Update

My query is as follows:

'select keyword, position, impressions, clicks, ctr 
 from keywords where profile_id=%s
 order by impressions desc limit %s, %s', (profile_id, start, end))

My table looks like this:

```
+---------------------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------------+---------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| profile_id | int(11) | YES | MUL | NULL | |
| landing_page_id | int(11) | YES | MUL | NULL | |
| keyword | varchar(2083) | YES | | NULL | |
| position | int(11) | YES | MUL | NULL | |
| impressions | int(11) | YES | MUL | NULL | |
| ctr | float | YES | MUL | NULL | |
| clicks | int(11) | YES | MUL | NULL | |
| unique_key | varchar(200) | YES | UNI | NULL | |
| position_30_days | int(11) | YES | | NULL | |
| impressions_30_days | int(11) | YES | | NULL | |
| clicks_30_days | int(11) | YES | | NULL | |
| ctr_30_days | float | YES | | NULL |

(DESCRIBE is not as descriptive as SHOW CREATE TABLE; we can't see what indexes you have.)

That one SELECT would benefit from this 'composite' index:

INDEX(profile_id, impressions) -- in that order.

Do you have a keyword that is 2083 characters long? If not, why have such a big VARCHAR?

Why have both unique_key and id? Is unique_key some form of UUID? They are notoriously inefficient when the table gets huge.

LIMIT ?, ? ... ($start, $end) -- The two numbers in LIMIT are start and count, not end.

By using the index, above, and changing to "remember where you left off", you can make the ORDER BY...LIMIT work a lot faster. Details . This suggestion, if practical for your application, will be faster (at least after the first 'page') than your original question about ordering the data could ever be! Why? Because OFFSET (the first number in LIMIT) requires work. My blog show how to get rid of that work.

More

When you could have multiple rows with the same value, and you need to be deterministic in ordering:

ORDER BY profile_id DESC, impression DESC, id DESC)
INDEX   (profile_id,      impression,      id)

Notes:

• In the ORDER BY, all the items are in the same direction (DESC is usually what is wanted).

• Mixing ASC and DESC prevents use of the index (until 8.0).

• Since you are looking for a single profile_id, ASC and DESC on it have identical effect.

To deal with a 'compound' $leftoff, let's look at the above example. After assuming that profile_id is constant, we want to somehow remember where you left off as a pair of $impression, $id, then do

WHERE   impression <= $impression
  AND ( impression <  $impression OR id < $id )

alternatively (and it is unclear whether these optimize differently in different versions of mysql):

WHERE ( impression = $impression AND id < $id
     OR impression < $impression )

StackExchange Database Administrators Q#164428, answer score: 4