CODEWITHSUNDEEP
mysqlcachingsortingpagination
lewicki
lewicki

Reputation: 479

MySQL Pagination - Should sorting be cached?

I can't find any information on this, so either MySQL handles this and is therefore a non-issue or I'm using the wrong keywords:

If someone wants to sort MySQL results, should I have a cached version of the sort somewhere or should i make my queries with an ORDER BY clause? IS there a better way to do this so that users are not sorting thousands of rows every time they change the page?

Upvotes: 0

Views: 301

Answers (2)

santiagobasulto
santiagobasulto

Reputation: 11706

You should create an index "with a sort criteria" on the column that you want to sort.

This is the syntax to create mysql index:

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_type]

index_col_name:
    col_name [(length)] [ASC | DESC]

So, if you usually retrieve some data sorted by some_column ordered DESC, you should:

CREATE INDEX my_index ON my_table (some_column DESC)

Upvotes: 1

Mark Byers
Mark Byers

Reputation: 838896

You should create an index on the column that wish to order by.

See the documentation ORDER BY Optimization for when MySQL can use an index to improve the performance of a query with ORDER BY.

Upvotes: 1

Related Questions