Poor Performance from ABS in WHERE

Question

I have a sqlite database with around 12,000 rows, the relevant columns are width1, height1, width2 and height2

My dynamic input is an arbitrary width and height, and I want to find all rows where the difference between the width and height are smaller than a x

To make it more compleicated, I'm never sure if the width and height of the input are reversed, so I also have to flip the values and test input width against stored height, etc.

Here's the ugly query:

SELECT id, width1, height1, width2, height2
FROM mytable
WHERE ((ABS(width - width1) < 10 AND ABS(height - height1) < 10) OR
       (ABS(height - width1) < 10 AND ABS(width - height1) < 10)) OR
      ((ABS(width - width2) < 10 AND ABS(height - height2) < 10) OR
       (ABS(height - width2) < 10 AND ABS(width - height2) < 10));

I have created combined indexes for width1, width2, height1, height2 as well as each column individually, but the query is missing the indexes.

Answer 1

You indexed the value of width1 but then you added another number to it and did the absolute value of that. The value has changed so much that the system no longer knows how to associate that to the index.

The answer here is to not do any math on width1, height1, width2, height2 but instead to do it on width and height only since those are passed in (not indexed values). This query isn't any prettier but it should turn to your indexes at least.

SELECT id, width1, height1, width2, height2
FROM mytable
WHERE 
    (
        (width1  between (width-10) and (width+10) 
            AND height1 between (height-10) and (height+10)) OR
        (width1  between (height-10) and (height+10) 
            AND height1 between (width-10) and (width+10)) OR 
        (width2  between (width-10) and (width+10) 
            AND height2 between (height-10) and (height+10)) OR
        (width2  between (height-10) and (height+10) 
            AND height2 between (width-10) and (width+10))
    )

Even better would be to pass in height_min, height_max, width_min, width_max (instead of just height and width) and then you don't have to do the math on every row:

SELECT id, width1, height1, width2, height2
FROM mytable
WHERE 
    (
        (width1  between (width_min) and (width_max) 
            AND height1 between (height_min) and (height_max)) OR
        (width1  between (height_min) and (height_max) 
            AND height1 between (width_min) and (width_max)) OR 
        (width2  between (width_min) and (width_max) 
            AND height2 between (height_min) and (height_max)) OR
        (width2  between (height_min) and (height_max) 
            AND height2 between (width_min) and (width_max))
    )