Poor Performance from ABS in WHERE

pythonsqlite

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.

Best Answer

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))
    )

Related Solutions

SQLite Write Ahead Logging – Why Are Files So Large?

The WAL file records all changes to the database since the last checkpoint.

(Automatic) checkpoints cannot run when the database is locked, so ensure that there is no other connection with an active transaction.

The documentation says:

By default, the auxiliary write ahead log and shared memory files used for transaction control are automatically deleted when the latest connection to the database closes.

So ensure that all database connections are closed at some time.

(If you have disabled deleting WAL files with SQLITE_FCNTL_PERSIST_WAL, you have to set PRAGMA journal_size_limit to a nonnegative value to truncate the WAL file to zero bytes in that situation.)

SQLite Performance – Improving GROUP BY Query Performance

Here is a suggestion: add an index on (seriesName, retreivalTime) and try this query. It won't be super fast but probably more efficient than what you have:

SELECT d.dbId,
       d.dlState,
       d.retreivalTime,
       d.seriesName,
        <snip irrelevant columns>
FROM DataItems AS d
  JOIN
    ( SELECT seriesName, 
             MAX(retreivalTime) AS max_retreivalTime
      FROM DataItems
      GROUP BY seriesName
      ORDER BY max_retreivalTime DESC
      LIMIT ?
      OFFSET ?
    ) AS di
    ON  di.seriesName = d.seriesName
    AND di.max_retreivalTime = d.retreivalTime
ORDER BY di.max_retreivalTime ;

Or (variation) using the PK as well, with an index on (seriesName, retreivalTime, dbId) and query:

SELECT d.dbId,
       d.dlState,
       d.retreivalTime,
       d.seriesName,
        <snip irrelevant columns>
FROM DataItems AS d
  JOIN
    ( SELECT dbId
      FROM DataItems
      GROUP BY seriesName
      ORDER BY MAX(retreivalTime) DESC
      LIMIT ?
      OFFSET ?
    ) AS di
    ON  di.dbId = d.dbId
ORDER BY d.max_retreivalTime ;

The logic behind the queries is to use only the index for the derived table calculations (finding the max(retreival-time) for every seriesName and then order by and do the offset-limit thing.)

Then the table itself will be involved only for fetching those 200 rows that are to be displayed.

Best Answer

Related Solutions

SQLite Write Ahead Logging – Why Are Files So Large?

SQLite Performance – Improving GROUP BY Query Performance

Related Question