Performance of different precedence pseudocolumn solutions with LIMIT

I was wondering about the relative performance of the solutions provided in the answers to a question on stackoverflow, I decided to run some tests.

The OP wanted to get the first matching row given a set of conditions with decreasing precedence. Both solutions involved a pseudocolumn, but one (mine) involved multiple SELECT statements UNION ALLed together, while the other one constructed a CASE expression.

I share my results in the hope that somebody will find this useful.

Your test design is flawed. You are testing incorrect results.

I added an answer to the question on SO you are referring to.

In your CASE version you can't add ORDER BY col1, col2. Would have to be ORDER BY precedence. But it would still be incorrect. You would have to ORDER BY the sum of scores for individual conditions to get rows fulfilling the most conditions first.

Similarly, your UNION ALL version yields incorrect results.

However, none of this seems necessary, there is a simpler and faster solution with UNION ALL. Refer to my answer to the SO question or use the sqlfiddle to play with

StackExchange Database Administrators Q#33007, answer score: 5