3

I have a large mysql table with two columns A and B. I want to SELECT from it ordering by A minus B.

I have tried indexing A and B separately as well as indexing A and B together. The query however takes several seconds regardless.

I haven't found anything about creating an index like (A-B), but I figured that this problem can't be that uncommon. Is there a solution for this?

The only workaround I came up with is to add an extra column A-B and index that, but it doesn't feel like a good solution.

Max
  • 488
  • 3
  • 12
  • Not index related, but using a derived table can _sometimes_ speed things up when ordering by a computed column; `SELECT * FROM (SELECT a,b,a-b AS c FROM myTable WHERE ...) ORDER BY c` – Joachim Isaksson Apr 12 '14 at 10:03
  • I don't really understand in which scenario this could speed things up. Regardless, I just tried it and it in this case it didn't help. – Max Apr 12 '14 at 10:22
  • Have you considered precomputing A-B in its own column? If you read more than you write, it should be worth it. (You could also consider using triggers to precompute the value for you, even though triggers themselves often slow things down). – Mifeet Apr 12 '14 at 10:24
  • Yes, the precomputing was what I meant with the workaround. Can you provide a link to the triggers you speak of? – Max Apr 12 '14 at 10:33
  • @Max The derived table approach will limit the number of times the calculation needs to be done when ordering. [Here](http://stackoverflow.com/a/14533000/477878) is a related answer re. the same thing. In your case, the expression is so simple that it may not make any difference though, the lack of an index is the bigger problem. – Joachim Isaksson Apr 12 '14 at 11:31
  • Interesting, thanks for the follow up! – Max Apr 12 '14 at 11:36

1 Answers1

3

What you ask is not possible in standard MySQL.

You're looking for a database object known to Oracle as a function-based index and to MariaDB as a a persistent virtual column.

O. Jones
  • 103,626
  • 17
  • 118
  • 172