How to optimize an ORDER BY for a computed column

问题:

I have a very large (80+ million row) de-normalized MySQL table. A simplified schema looks like:

+-----------+-------------+--------------+--------------+
|    ID     |   PARAM1    |   PARAM2     |   PARAM3     |
+-----------+-------------+--------------+--------------+
|    1      |   .04       |    .87       |    .78       |
+-----------+-------------+--------------+--------------+
|    2      |   .12       |    .02       |    .76       |
+-----------+-------------+--------------+--------------+
|    3      |   .24       |    .92       |    .23       |
+-----------+-------------+--------------+--------------+
|    4      |   .65       |    .12       |    .01       |
+-----------+-------------+--------------+--------------+
|    5      |   .98       |    .45       |    .65       |
+-----------+-------------+--------------+--------------+

I'm trying to see if there's a way to optimize a query in which I apply a weight to each PARAM column (where weight is between 0 and 1) and then average them to come up with a computed value SCORE. Then I want to ORDER BY that computed SCORE column.

For example, assuming the weighting for PARAM1 is .5, the weighting for PARAM2 is .23 and the weighting for PARAM3 is .76, you would end up with something similar to:

SELECT ID, ((PARAM1 * .5) + (PARAM2 * .23) + (PARAM3 * .76)) / 3 AS SCORE 

ORDER BY SCORE DESC LIMIT 10

With some proper indexing, this is fast for basic queries, but I can't figure out a good way to speed up the above query on such a large table.

Details:

• Each PARAM value is between 0 and 1
• Each weight applied to the PARAMS are between 0 and 1 s

--EDIT--

A simplified version of the problem follows.

This runs in a reasonable amount of time:

SELECT value1, value2 
FROM sometable 
WHERE id = 1 
ORDER BY value2

This does not run in a reasonable amount of time:

 SELECT value1, (value2 * an_arbitrary_float) as value3 
 FROM sometable 
 WHERE id = 1 
 ORDER BY value3

Using the above example, is there any solution that allows me to do an ORDER BY with out computing value3 ahead of time?

回答1:

I've found 2 (sort of obvious) things that have helped speed this query up to a satisfactory level:

1. Minimize the number of rows that need to be sorted. By using an index on the 'id' field and a subselect to trim the number of records first, the file sort on the computed column is not that bad. Ie:

   SELECT t.value1, (t.value2 * an_arbitrary_float) as SCORE
   FROM (SELECT * FROM sometable WHERE id = 1) AS t 
   ORDER BY SCORE DESC
   

2. Try increasing sort_buffer_size in my.conf to speed up those filesorts.

回答2:

I know this question is old, but I recently ran into this problem, and the solution I came up with was to use a derived table. In the derived table, create your calculated column. In the outer query, you can order by it. It seems to run considerably faster for my workload (orders of magnitude).

SELECT value1, value3
FROM (
    SELECT value1, (value2 * an_arbitrary_float) as value3 
    FROM sometable 
    WHERE id = 1 
) AS calculated
ORDER BY value3

回答3:

MySQL lacks many sexy features that could help you with this. Perhaps you could add a column with the calculated ranking, index it and write a couple of triggers to keep it updated.