0

Consider the following table:

CREATE TABLE `multiqueue` (
    `ID` BIGINT(20) NOT NULL AUTO_INCREMENT,
    `CustomerID` BIGINT(20) NOT NULL,
    `Volume` INT(11) NOT NULL,
    `Content` MEDIUMTEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `PublishedTS` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`ID`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
ENGINE=InnoDB
;

This table serves as a multi-queue, meaning that it aggregates the queues of requests coming from multiple customers (denoted by CustomerID), each request having a certain Volume of work.

How to write a query that will select top N rows from the table, interleaving the rows from different customers?

If customer 1 sends 100 requests, each of volume 1000, then customer 2 sends 20 requests, each of volume 300, I would like that the query doesn't force any of the customers to starve on responses while my program is busy handling the requests of another customer. It should take 1 request of customer 1 and 3-4 requests of customer 2 in the first fetch, process them, then take 1 more request of customer 1 and 3-4 requests of customer 2, and so on.

What I have tried so far:

SET @runtot := 0;
SELECT q1.id1, q1.customerId1, q1.volume1, q1.content1, (@runtot := @runtot + q1.volume1) AS rt
FROM (
  SELECT ID AS id1, CustomerID AS customerId1, Content AS content1
  FROM multiqueue
  ORDER BY id1
) AS q1
WHERE @runtot < 2000

As described here, the code above limits the number of items selected with a running total of some field in the rows selected. In the scenario above, customer 2 would starve when the above query is in use.

The database in use is MariaDB (version 10.4.13, but I can upgrade to the latest if needed), though a solution for MySQL should also work.

4
  • maradb looses the order of the subquery, so you need to add a limit: further, to satisfy all customers, you must have a loop,, that runs toll all request are done, and a knowledge of all fulfilled requests already.
    – nbk
    Jun 21 '20 at 18:07
  • What version of mysql, MariaDB?
    – Lennart
    Jun 21 '20 at 18:07
  • @Lennart, I've edited the question to specify the version. Basically you can assume the latest version. Jun 22 '20 at 1:43
  • 1
    Ok, then you can have a look at my answer. MariaDB 10,MYSQL 8 should be ok with that
    – Lennart
    Jun 22 '20 at 1:48
1

For recent versions, you can use window functions:

SELECT ID, CustomerID, Volume, Content, runtot
FROM ( 
    SELECT ID
         , CustomerID
         , Content
         , sum(Volume) over (
                partition by CustomerID 
                order by ID
           ) as runtot
    FROM multiqueue
) AS q1 
WHERE runtot < 2000;

EDIT:

If you want at least 1 row for each customer, you can add another window function ROW_NUMBER() that enumerates the result, and use that in your select:

SELECT ID, CustomerID, Volume, Content, runtot
FROM ( 
    SELECT ID
         , CustomerID
         , Content
         , sum(Volume) over (
                partition by CustomerID 
                order by ID
           ) as runtot
         , row_number() over (
                partition by CustomerID 
                order by ID
           ) as rn

    FROM multiqueue
) AS q1 
WHERE runtot < 2000
   OR rn = 1;
5
  • Thanks for your answer. It mostly works, but I've read mariadb.com/kb/en/window-functions-overview and I still don't understand how it works. Could you elaborate? Also, when all the requests have a Volume of 2000 or greater, the query produces no results. How to change the query so that it produces at least one row for each customer even if that row's Volume exceeds 2000? Jun 22 '20 at 4:12
  • Also, if the query has a filter condition like WHERE PublishedTS IS NULL, do I need to add an index like (PublishedTS, CustomerID, ID)? Or in general, what indexes does such query need? It takes several seconds on a table having ~2K records for me without additional indexes. Jun 22 '20 at 4:21
  • Regarding window functions, have you had a look at the more elaborative examples at MariaDB like: mariadb.com/resources/blog/…
    – Lennart
    Jun 22 '20 at 4:50
  • Is PublishedTS an attribute that you excluded from your table? The index you mention probably helps. A filtered index would probably be the best choise, but AFAIK MySQL, MariaDB does not support them.
    – Lennart
    Jun 22 '20 at 4:58
  • Yes, I excluded the majority of fields with PublishedTS among them. I've edited the question to add it back. Jun 22 '20 at 5:03
0

"Don't queue it, just do it." That is, don't bother to put the request(s) in a queue(s), spin off a process to work through the task(s). That leaves it up to the OS to share resources. And avoids several nasties that may happen when you try to use MySQL as a Queue.

1
  • Some problems with this approach: 1) Durability: If the process terminates, the task is lost. 2) Multi-processing: actually we use the DB to distribute the tasks from multiple producers to multiple consumers. 3) Result storage: the DB is backup storage of the task results in case inter-process communication is not immediately available. Jun 24 '20 at 3:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.