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 to sort.

Can I put an index in it?

Answer 1

Generally yes. If you do ORDER BY in this field, it should probably be indexed. Of course, you will first want to check it out to make sure that it really helps - if you only ever select a small number of lines, it may not have a big impact.

Answer 2

Are you honestly expecting to have a sort_order value that lights up at 9,223,372,036,854,775,807 ?! Assuming zero-based, INT is still quite large with a maximum size of 2,147,483,647 ...

Depends on your requests, but I would look at its use in the coverage index before offline. MySQL has a limit on the number of indexes , you are likely to hit the ceiling if you define an index per column:

Prefix support and prefix lengths (if supported) depend on the storage engine. For example, a prefix can contain up to 1000 bytes for MyISAM tables and 767 bytes for InnoDB tables.

Answer 3

As Eric mentioned above, the answer is: Yes.

Although, if you really make a lot of investments and updates in the table, MySQL builds a separate block of information for indexing, which needs to be updated every time changes occur to the table. Therefore, overhead may be in some cases.

So basically this is a mixed case, and circumstances should always be considered.

Answer 4

Generally not. You justify indexes by searches. By the time you have reduced the number of entries to the number that you usually display (say, less than a few hundred) with an index, you are not buying anything.

Therefore, add only the index if you will use the field for selection (for example, including "LIMIT 500").