0

We have an app, with multiple threads, which are grabbing lines from a table for processing.

Imagine queries like this:


UPDATE our_table 
SET 
content_status = 'IN_PROGRESS', worker = 'worker_4' 
WHERE 
content_upload_status = 'ADD' and content_status = 'DEFAULT';

Now, this runs for 2-3-4 minutes sometimes, and i don't know why. Sometimes is is super quick.


           id: 1
select_type: SIMPLE
        table: books_to_reconcile_websites
         type: index_merge
 possible_keys: content_upload_status,content_status
          key: content_upload_status,content_status
      key_len: 1,1
          ref: NULL
         rows: 120510
        Extra: Using intersect(content_status,status); Using where; Using buffer

If i run a SELECT to just get the rows, i get 0 lines (right now, of course, you would have more rows usually) and it takes 2-3 seconds.

I tried adding an ORDER BY to the update, no impact. I tried removing LIMIT, no impact.

When i was running this query, no other process has in the processlist working with this table.

We are on 10.4.13-MariaDB

Not sure what else i can check.

Zsolt János
  • 491
  • 8
  • 18

1 Answers1

0

A composite index on both columns from there WHERE clause solved it.

Based on this: Why would a simple MySQL update query occasionally take several minutes?

Zsolt János
  • 491
  • 8
  • 18
  • While this link may answer the question, it is better to include the essential parts of the answer here and provide the link for reference. Link-only answers can become invalid if the linked page changes. - [From Review](/review/low-quality-posts/34096157) – Florian Winter Mar 24 '23 at 09:56
  • 2
    My first sentence contains the solution. – Zsolt János Mar 24 '23 at 17:49
  • Got it. Do you think that information is enough, or would anyone else seeing this answer want more details? – Florian Winter Mar 27 '23 at 08:32