StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to optimize an ORDER BY for a computed column on a MASSIVE MySQL table
text
Body
copied!<p>I have a very large (80+ million row) de-normalized MySQL table. A simplified schema looks like:</p> <pre> +-----------+-------------+--------------+--------------+ | ID | PARAM1 | PARAM2 | PARAM3 | +-----------+-------------+--------------+--------------+ | 1 | .04 | .87 | .78 | +-----------+-------------+--------------+--------------+ | 2 | .12 | .02 | .76 | +-----------+-------------+--------------+--------------+ | 3 | .24 | .92 | .23 | +-----------+-------------+--------------+--------------+ | 4 | .65 | .12 | .01 | +-----------+-------------+--------------+--------------+ | 5 | .98 | .45 | .65 | +-----------+-------------+--------------+--------------+ </pre> <p>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.</p> <p>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:</p> <pre><code>SELECT ID, ((PARAM1 * .5) + (PARAM2 * .23) + (PARAM3 * .76)) / 3 AS SCORE ORDER BY SCORE DESC LIMIT 10 </code></pre> <p>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. </p> <p>Details: </p> <ul> <li>Each PARAM value is between 0 and 1</li> <li>Each weight applied to the PARAMS are between 0 and 1 s</li> </ul> <p><strong>--EDIT--</strong></p> <p>A simplified version of the problem follows.</p> <p>This runs in a reasonable amount of time:</p> <pre><code>SELECT value1, value2 FROM sometable WHERE id = 1 ORDER BY value2 </code></pre> <p>This does <em>not</em> run in a reasonable amount of time:</p> <pre><code> SELECT value1, (value2 * an_arbitrary_float) as value3 FROM sometable WHERE id = 1 ORDER BY value3 </code></pre> <p>Using the above example, is there any solution that allows me to do an ORDER BY with out computing value3 ahead of time?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload