CODEWITHSUNDEEP
mysqlsorting
gentlejo
gentlejo

Reputation: 2710

Can I add special column for sorting to mysql?

My 'contents' table has about 10,000,000 rows.

I used column 'register_date' for sorting in contents list. But now I want to use 'register_date' column for another purpose. So, I'm about to add new column like 'seq (bigint)' for sorting. And add index to seq column.

If new row added, 'seq' filled with max seq value. And if older row need to be appear head of list, will be changed the 'seq' value to newer max seq value.

In this case, calculating max seq value, inserting, updating is expensive? I don't know which way is good.

Upvotes: 1

Views: 95

Answers (1)

wallyk
wallyk

Reputation: 57784

If the proposed seq value is not searched for (such as in a WHERE clause), then don't index it at all. Indexing something which is not searched for, but frequently updated results in lower performance.

As long as most retrieved results are a fraction of the table, SORT BY seq can efficiently manage the sort without an index. The big question is: at what point does it make sense to have it indexed? To answer that, some benchmarking is necessary.

Upvotes: 3

Related Questions