MYSQL query reducing the inner query in conditions

Question

I am running into a small problem,

This is a demo query

select 
    A.order_id, 
    if(
         A.production_date != '0000-00-00', 
         A.production_date,
         if(
              SOME INNER QUERY != '0000-00-00', 
              SOME INNER QUERY ,
              SOME OTHER INNER QUERY
           )
    ) as production_start_date
from
    orders A

So basically, suppose the SOME INNER QUERY is taking 10 seconds to do its calculations, fetching data from 8 different tables, checking past history for same order type etc. and if its result is a date, I fetch that date in first condition. But now it will take 20 seconds as 10 seconds for calculation for if condition, and 10 seconds to re-execute to return the result.

Is there any way I can reduce this?

if any one is interested in looking at actual query http://pastebin.com/zqzbpEei

Answer 1

Assuming your query looks like this (sorry, I gave up trying to locate the actual query):

IF(
    (SELECT aField FROM aTable WHERE bigCondition) != '0000-00-00',
    SELECT aField FROM aTable WHERE bigCondition,
    SELECT anotherField FROM anotherTable
)

You can rewrite it as follows:

SELECT IF (
    someField != '0000-00-00',
    someField,
    SELECT anotherField FROM anotherTable
)
FROM aTable WHERE bigCondition

This way you compute bigCondition only once.

---

This query is quite ugly indeed.

Your major problem seems to be the misuse (and abuse, big time) of the IF() construct. It should be reserved to simple conditions and operations. The same applies to logical operators. Do not operate on entire queries. For instance, I see this one bit appears a few times in your query:

IF(
    (SELECT v1.weekends FROM vendor v1 WHERE v1.vendor_id = A.vendor_id) IS NULL
       OR (SELECT v1.weekends FROM vendor v1 WHERE v1.vendor_id = A.vendor_id) = '',
    '6', -- by the way, why is this a string?! This is an integer, isn't it?
    (SELECT v1.weekends FROM vendor v1 WHERE v1.vendor_id = A.vendor_id)
)

This is Bad. The condition should be moved into the SELECT directly. Rewrite it as below:

SELECT
    IF (v1.weekends IS NULL OR v1.weekends = '', 6, v1.weekends)
FROM vendor v1 WHERE v1.vendor_id = A.vendor_id

That's two SELECT saved. Do this for every IF() that contains a query, and I am ready to bet you are going to speed up your query by several orders of magnitude.

There is a lot more to say about your current code. Unfortunately, you will probably need to refactor some parts of your ORM. Add new, more specialised methods to some classes, and make them use new queries that you crafted manually. Then refactor your current operation so that it uses these new methods.