0

I have java app that is using MySQL in the backend. I have the following table:

A = int, B = varchar, C = timestamp

A | B   | C
1 | 100 | 2022-03-01 12:00:00
2 | 200 | 2022-03-01 12:00:01
3 | 100 | 2022-03-01 12:00:01
4 | 200 | 2022-03-01 12:00:02
5 | 600 | 2022-03-01 12:00:03
1 | 100 | 2022-03-01 12:00:06
5 | 700 | 2022-03-01 12:00:07
2 | 200 | 2022-03-01 12:00:08
9 | 100 | 2022-03-01 12:00:08

On every X seconds, query should be run, and it should process 5 records where column C > LAST_PROCESSED_TIMESTAMP. This LAST_PROCESSED_TIMESTAMP is updated after each run.

What I need is - I want to select these 5 rows, but not to include the rows if columns A and B are going to repeat in some fetches that are going to happen in the future.

Example: for table above:

First run - select 5

1 | 100 | 2022-03-01 12:00:00 <-- NOT SELECTED SINCE IT IS IN 2nd RUN

2 | 200 | 2022-03-01 12:00:01 <-- NOT SELECTED SINCE IT IS IN 2nd RUN

3 | 100 | 2022-03-01 12:00:01
4 | 200 | 2022-03-01 12:00:02
5 | 600 | 2022-03-01 12:00:03

Seconds run - select 5

1 | 100 | 2022-03-01 12:00:06
5 | 700 | 2022-03-01 12:00:07
2 | 200 | 2022-03-01 12:00:08
9 | 100 | 2022-03-01 12:00:08

In first run, we do not select 1|100 and 2|200, since it will be selected in second run.

I already have some solutions for this, but selects are taking way too much time. The database is also huge - so I'm trying to find the fastest way to execute this. Which indices should I have to optimize this? Which kind of query?

What I tried:

SELECT  *
    FROM ( SELECT  A,B
            FROM  TABLE
            WHERE  C >= '2022-03-01 12:00:00'
            LIMIT  5
         ) a
    LEFT JOIN (
        SELECT  A,B
            FROM  TABLE
            WHERE  C >= '2022-03-01 12:00:00'
            LIMIT  5, 18446744073709551615
              ) b  ON (     a.A=b.A
                       AND  a.B=b.B
                      )
    WHERE  b.A IS NULL;

and also (this one is probably NOT OK, since it will select MAX of C even if not in first 5, so for my example, it would include 2 | 200 | 2022-03-01 12:00:08 inside of the first run - not what I need):

SELECT  A, B, MAX(C)
    FROM  TABLE
    WHERE  C >= '2022-03-01 12:00:00'
    GROUP BY  A, B ASC
    LIMIT  5;
0

2 Answers 2

0

I already have some solutions for this, but selects are taking way too much time.

Create an index by (C,A,B). Then look for execution plan - if the index is not used then try to force its usage.

What I tried

Excessively complex. Use

SELECT * 
FROM table 
WHERE C >= '2022-03-01 12:00:00' 
ORDER BY C, A, B LIMIT 5;

On every X seconds, query should be run, and it should process 5 records where column C > LAST_PROCESSED_TIMESTAMP. This LAST_PROCESSED_TIMESTAMP is updated after each run.

Not enough for deterministic processing.

Imagine that there are 6 rows with the same timestamp in C, which differs in A and B. Not more than 5 of them should be processed, and their timestamp is saved. Next run this timestamp is applied... but now I do not see the way to determine what rows of there 6 ones were processed already, and what other are to be processed now.

Add a column which will store a mark "this row was already processed". Use this column in the query which selects 5 rows for current processing.

4
  • Can you please tell me if this query is going to filter rows where A and B are duplicated? This one is only limiting to 5 results. Related to your other comment (deterministic) - you are right - here I would need to have LIMIT = 5 + delta where delta = 1 in your example. I would find delta with separate query. Commented Mar 8, 2022 at 8:44
  • @BojanVukasovic Can you please tell me if this query is going to filter rows where A and B are duplicated? ??? Do you have another question? If so then create separate topic with it.
    – Akina
    Commented Mar 8, 2022 at 10:32
  • Not a new question, but the query you proposed me to use is not the same as the use-case I need. That is why I asked if I am missing something here, since I cannot see that it would filter rows having same A and B columns. Commented Mar 8, 2022 at 10:48
  • No, The question tells nothing about filtering the duplicates, and my query won't do this.
    – Akina
    Commented Mar 8, 2022 at 10:50
0

These would help your first attempt:

INDEX(C,A,B)
INDEX(A,B,C)

In order to figure out how to simplify or improve the query, start by tossing b. It adds nothing but complexity to the question. Think through the question with only a and c.

More

  1. Pull 5 rows off the "input queue"
  2. Identify "dups" (perhaps with LEFT JOIN and IS NOT NULL, or with NOT EXISTS ( SELECT 1 ... )
  3. Store the non-dups (perhaps with INSERT ... in front of the SELECT in step 2)
  4. push the dups back into the input queue (DELETE from the queue?)`

A "dup" is where A and B both match.

8
  • The B is there in order to filter the rows, since if A and B are showing up in some later row (so B has to be equal also), then I need to filter it out. Otherwise, if only A==A then I leave both rows in place. Commented Mar 8, 2022 at 8:40
  • @BojanVukasovic - Your example implied that the combination of A and B acted like a single thing to uniquely identify the row. I'm trying to transform the statement of the problem in order to focus on something simpler.
    – Rick James
    Commented Mar 8, 2022 at 15:09
  • you can see from example that 1|100 is showing up twice in table, so it is not unique key. I think there is not other way except to try with the indexes as you said. Commented Mar 8, 2022 at 15:17
  • @BojanVukasovic - You eventually want to display each "1|100" row, correct? So, as you imply, the "primary key" is more important than A and B when figuring out whether some row has already been displayed.
    – Rick James
    Commented Mar 8, 2022 at 15:20
  • No, I wanted to filter duplicate rows that appear at the later dates. So use-case specific to my example: my program has memory to store only 5 rows at once. So when it selects the first 5 rows as in my example, I do not want to select rows that I will select in the next iteration (1|100 will be selected also on 2nd run, so it should be skipped on first run - it will be anyway processed on 2nd run do delay it a bit). Commented Mar 8, 2022 at 15:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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