Recently I had a pretty decent-sized client have a significant slowdown on their page load speed. I started digging in and found that they had introduced some new MySQL queries. In one query in particular they were creating 2 derived fields. One field used a CONCAT to do string manipulation on the fly and the other doing some math with a ROUND command. Then they did an ORDER BY on those two fields.

Normally this wouldn’t be a huge deal. If you wanted to do an ORDER BY on a standard field and it was slow you could add an index that would pre-sort your fields and save the order to the disk as an index but because these two fields were dynamically generated there was no way to add an index. MySQL had no real way of knowing what the derived value of those fields will be so it cannot pre-sort an index. This means every time this query was done, not only did MySQL have to calculate those 2 values for every single record, it also had to sort every single record based on those two fields. That means an exponential amount of comparison operations.

What should they have done? At minimum try to avoid sorting derived fields. You could calculate the value of those derived fields right before you save the record then save those derived fields as regular fields. This would have the drawback of a slightly larger record size so that would take up more disk space and depending on how you structure your selects have greater network throughput. 

Actually for this specific example as I look at the code the mathematical function divides the stored value by a constant and then rounds it. Sorting by the stored field would essentially have the same effect as sorting by the derived field so just sort by the stored field there. Secondly, the concatenated derived field is just two of the stored fields so just sort by the first field and then the second, no need to concatenate anything.

Bottom line: Avoid fancy sorts and other resource-heavy operations on dynamically generated fields and you will save yourself a lot of headaches.

~Cheers!