Suppose I have a table of scores that I precomputed based on some data elsewhere. I want to be able to search this data multiple times per second to get the top x results. The search will weight the scores, and the weights will change constantly. What can I do to speed up this query? I made an example that is a good representation of how I'll be doing things, minus foreign keys to another table, more data, and some other (I hope) inconsequential stuff.

USE clashroyale;
DROP TABLE if exists stackoverflowExample;
CREATE TABLE stackoverflowExample(id int AUTO_INCREMENT, score1 float, score2 float, score3 float, PRIMARY KEY(id));

INSERT INTO stackoverflowExample (score1, score2, score3) VALUES(2,1,-1), (1.12,4.2,3.2);
SELECT *, 0.6*score1 0.3*score2 0.1*score3 as weightedScore FROM stackoverflowExample ORDER BY weightedScore DESC LIMIT 10;

I don't think indexing would work, because no row can be ruled out until it is fully processed so every row must be processed.

CodePudding user response：

If the weights stayed the same, you could create a stored generated column, so the weighted score is computed when you insert the data. Then add an index on that generated column so the ORDER BY becomes a no-op.

But you said the weights do not stay the same. The weighted score must be calculated potentially differently when you run the query. That means there's no way to index the sort order either. There's no benefit to making an index that will be used only once; it's at least as expensive to build the index as it is to simply do the sort once.

I don't see any way of optimizing this query any further. If it's not running fast enough for you, then upgrade to a faster computer to run your database.

Other optimizations would require you rethink how you are calculating the weighted score. Perhaps there's a way to precalculate several different weightings, if the weightings are predictable. Or you could cache the results so you don't have to run the query so frequently.