CODEWITHSUNDEEP
query-optimizationmariadb
jbh
jbh

Reputation: 1153

MariaDB simple join with order by without temp table

I have a job queue that is FIFO and can grow to the range of 0 to 10MM records. Each record has some value associated with a user. I have a second table that CAN contain USERS that have priority. This gets queried a lot by worker threads. This causes slow queries in the 1MM record range when ordering by this priority e.g.

select  *
    from  calcqueue
    LEFT JOIN  calc_priority USING(userId)
    where  calcqueue.isProcessing IS NULL
    order by  ISNULL(calc_priority.priority), calc_priority.priority

running explain on this gets me "Using index condition; Using temporary; Using filesort". I attempted to switch this over to a derived table which scales at larger number of rows, however I cant get the order to stay preserved which defeats the true intentions (but at least keeps my servers speedy)

SELECT  *
    FROM  
      ( SELECT  priority,p,userId FROM
              ( SELECT  calc_priority.priority,
                        qt_uncalc.userId,
                        ISNULL(calc_priority.priority) p
                    from  
                      ( SELECT  userId
                            from  calcqueue
                            WHERE  isProcessing IS NULL
                      ) qt_uncalc
                    LEFT JOIN  calc_priority USING(userId) sortedQ
                    ORDER BY  p,sortedQ.priority ASC
              ) orderedT

Is there anyway to achieve this only using derived tables? calc_priority can (and does) change a lot. So adding the priority in at calcqueue insert time isn't an option

Upvotes: 0

Views: 256

Answers (1)

Rick James
Rick James

Reputation: 142296

Plan A

Munch on this:

      ( SELECT  *, 999999 AS priority
            from  calcqueue
            LEFT JOIN  calc_priority USING(userId)
            where  calcqueue.isProcessing IS NULL
              AND  calc_priority.priority IS NULL
            LIMIT  10 
      )
    UNION  ALL
      ( SELECT  *, calc_priority.priority
            from  calcqueue
            JOIN  calc_priority USING(userId)
            where  calcqueue.isProcessing IS NULL
            ORDER BY  calc_priority.priority
            LIMIT  10 
      )
    ORDER BY  priority

and include

    LIMIT  10; INDEX(isProcessing, userId)

I'm attempting to avoid the hassle with NULL.

Plan B

You could change the app to always set priority to a suitable value, thereby avoid having to do the UNION.

Upvotes: 1

Related Questions