1

Here's a rather simple SQL query:

select source_id
from data_sources
join sources on sources.id = data_sources.source_id
where log_time + interval sources.retention_days day < current_timestamp;

There are ~800 records in sources and ~60 million records in data_sources.

The DBMS used is Maria DB:

mysqld  Ver 10.3.22-MariaDB-1ubuntu1 for debian-linux-gnu on x86_64 (Ubuntu 20.04)

Indizes are being used apparently:

+--+-----------+------------+-----+-------------------------------+-------------------------------+-------+----------------------+----+-----------+
|id|select_type|table       |type |possible_keys                  |key                            |key_len|ref                   |rows|Extra      |
+--+-----------+------------+-----+-------------------------------+-------------------------------+-------+----------------------+----+-----------+
|1 |SIMPLE     |sources     |index|PRIMARY,idx_sources_1          |index_sources_on_retention_days|4      |NULL                  |757 |Using index|
|1 |SIMPLE     |data_sources|ref  |index_data_sources_on_source_id|index_data_sources_on_source_id|5      |vse_web_dev.sources.id|445 |Using where|
+--+-----------+------------+-----+-------------------------------+-------------------------------+-------+----------------------+----+-----------+

Surprisingly this query takes about an hour on an 8 core Intel i7. RAM doesn't seem to be a problem: While the MySQL processes are causing a very high CPU load on multiple cores during execution time, there is plenty of free RAM.

Here the DDLs for the tables involved:

create table sources (
    id int auto_increment primary key,
    name varchar(255) null,
    source varchar(255) null,
    start_time varchar(255) null,
    frequency varchar(255) null,
    stop_time varchar(255) null,
    unit varchar(255) null,
    created_at datetime not null,
    updated_at datetime not null,
    type varchar(255) null,
    move_source_file tinyint(1) default 1 null,
    revision int null,
    data_freq_minute int default 1 null,
    shift_right_time_stamp tinyint(1) default 0 null,
    display_name varchar(255) null,
    source_group_id int null,
    retention_days int default 365 not null
);

create index idx_sources_1 on sources (id);
create index index_sources_on_source_group_id on sources (source_group_id);
create index index_sources_on_retention_days on sources (retention_days);

create table data_sources (
    id bigint auto_increment primary key,
    source_id int null,
    log_time datetime null,
    value float null,
    created_at datetime not null,
    updated_at datetime not null
);

create index idx_data_sources_1 on data_sources (id);
create index idx_data_sources_2 on data_sources (id, source_id, log_time);
create index index_data_sources_on_log_time on data_sources (log_time);
create index index_data_sources_on_source_id on data_sources (source_id);
create index index_data_sources_on_updated_at on data_sources (updated_at);


Why is this taking so long and how to speed it up?

6
  • 2
    I followed from SO, here I guess is better place to post your question, so I think the issue here, you are doing a dynamic calculation with a join on large data set, my suggestion might not be a solution but can give some hint, why not pre-calculate your where statement condition in a field and index it, then use it as a normal field in the query statement.
    – ROOT
    May 21, 2020 at 9:36
  • @nbk The DDL I posted was outdated and I missed it. Updated. May 21, 2020 at 9:59
  • @ROOT Thanks for the pointer. Will try. May 21, 2020 at 9:59
  • 1
    @ROOT Creating the pre-calculated column with an index will probably take as long as the first approach -- all in all. Of course with the benefit of improved execution time of the UPDATE. Unfortunately, retention_days may change often and in fast succession. So maintaining the index seems a bit unpractical in my use case. May 21, 2020 at 10:05
  • The best apprtoach is to get rid iof the join and swtch to where in, but your logic for the time selection hinders this approach,
    – nbk
    May 21, 2020 at 10:35

2 Answers 2

1

Adding an index to data_sources(source_id, log_time) solved the problem.

create index index_data_sources_source_id_log_time on data_sources(source_id, log_time);

This brought the execution time down to a couple of seconds.

Apparently the calculation of interval sources.retention_days day < current_timestamp doesn't have such a massive performance impact, as I suspected.

Thanks for the helpful comments!

1

Plan A:

where log_time + interval sources.retention_days day < current_timestamp;

is not "sargable". Rearrange it so that the column is on one side of an operator, then make sure there is an index (which you do):

WHERE data_sources.log_time <
      NOW() - INTERVAL sources.retention_days DAY

Also needed:

data_sources:  (source_id, log_time)

Plan B:

I don't have a lot of confidence in the Plan A. So, here is another thing to try: (working....)

[The query does not make sense; see my Comment.]

Unrelated

Toss two redundant indexes; you have

id bigint auto_increment primary key,

These are redundant:

create index idx_data_sources_1 on data_sources (id);
create index idx_data_sources_2 on data_sources (id, source_id, log_time);
4
  • Thanks, very appreciated. You are absolutely right in your comment, it's a bit weird to select source_id multiple times. What you're witnessing here is my spineless attempt to resemble the result of a legacy system -- touching downstream code in this project would surely open the gates of hell. May 25, 2020 at 15:55
  • "gates of hell" in a legacy system. I do understand.
    – Rick James
    May 26, 2020 at 0:16
  • MySQL defines PRIMARY KEY as being both an index and "clustered with the data. Hence, those two indexes are redundant for MySQL. That is, perhaps there were useful for the 'legacy' system.
    – Rick James
    May 30, 2020 at 1:52
  • Thanks for the pointer. I don't think they exist for a purpose. Dropped. Jun 1, 2020 at 9:26

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.