Should I index my sort fields in MySQL

Question

I have a field called 'sort_order' and it's bigint, i use it in my mysql queries for sorting.

Is it wise I put an index on it?

Answer 1

Generally, yes. If you are doing an ORDER BY on that field, it should probably be indexed. Of course you'll want to test it first to make sure it actually helps - if you only ever select a small number of rows it may not make that much of a difference.

Answer 2

Do you honestly expect to have a sort_order value that maxes out at 9,223,372,036,854,775,807?! Assuming zero based, INT is still pretty large at a max of 2,147,483,647...

Depends on your queries, but I'd look at using it in a covering index before a stand alone one. MySQL has a space limit on indexes, you're likely to hit the ceiling if you define an index per column:

Prefix support and lengths of prefixes (where supported) are storage engine dependent. For example, a prefix can be up to 1000 bytes long for MyISAM tables, and 767 bytes for InnoDB tables.

Answer 3

As Eric already mentioned above, the answer is Yes.

Although, if you are actually doing a lot of inserts and updates in the table, MySQL builds a separate block of information for indexing that needs to be updated every time there are changes made to the table. Thus the overhead can be there in some cases.

So basically it's a mixed case and the circumstances should always be considered.

Answer 4

Generally, no. You justify indexes with searches. By the time you've reduced the record count to the number you normally display (say, less than several hundred) having an index doesn't buy you anything.

So only add an index if you will use the field for selecting (which would include, say, "LIMIT 500" for instance.)