Sortable columns dilemma

[Patrick Clas]

I have an HTML table with about 10 sortable columns. This table is basically reflected verbatim in a mysql table. This table is composed of approximately 200,000 people, and can potentially grow to over a million. The table can also be filtered by a given country, and all the columns are still sortable. The only way I know how to handle these sorts quickly would be to add 20 indices, 10 for the regular table and 10 for the table filtered by country.

The problem is, this table also has frequent updates, so adding all these indices is potentially a performance issue. Every column that is sortable has data that is potentially updated frequently, so moving some data to another table doesn't seem like it would help.

I am not experienced with partioning, but I thought maybe that would be something useful for the country filter? On the other hand, I don't know how that would really help the performance impacts to inserts/updates.

So my question is, what's the right process to go through here and do I have any other options? Should I just add all 20 indices and see how badly it affects the performance of inserts/updates? I've learned a lot about mysql, but I'm by no means an expert. I could really use some helpful advice here.

I'm happy yo give more details as desired. :)