Sorting of big table in mySQL
Posted on 2013-11-26
I have a table which consists purely of number values but it's big in terms of records (400K) and columns (80). The data on the table do not change at all.
Here is the structure:
id, sorting_column1, sorting_column2, ... sorting_column80
sorting_columns are decimal(5,2).
The queries I perform are like:
select id from mytable order by (sorting_column1*4 + sorting_column6*2 + sorting_column1*7) desc limit 100
however, the sorting statements is 100% dynamic so I can't have all the possible sorting combinations indexed on other columns. I do however have index on every column separately which helps with the calculation.
What is the best way to make this as fast as possible? I can store the data in memory since they do not change at all. Shall I use a search server instead of mysql for example?
Thank you a lot for your time!