Как оптимизировать таблицы/запрос в MySQL?

CREATE TABLE `genes-g38-201505` (
  `chr` varchar(2) NOT NULL,
  `left` bigint(20) NOT NULL,
  `right` int(11) NOT NULL,
  `Complement` int(11) NOT NULL,
  `Name` tinytext NOT NULL,
  `source` tinytext NOT NULL,
  `ENSEMBL` tinytext NOT NULL,
  `gene_version` tinytext NOT NULL,
  `gene_name` tinytext NOT NULL,
  `gene_source` tinytext NOT NULL,
  `gene_biotypeid` tinytext NOT NULL,
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=MyISAM;

CREATE TABLE `repeats-g38-201505` (
  `id` int(11) NOT NULL,
  `chr` varchar(2) DEFAULT NULL,
  `left` int(11) DEFAULT NULL,
  `right` int(11) DEFAULT NULL,
  `name` tinytext,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;

CREATE TABLE `51k-80-80-ignore-random-noreverse` (
  `chr` varchar(2) NOT NULL,
  `left` bigint(20) NOT NULL,
  `right` bigint(20) NOT NULL,
  `count` int(11) NOT NULL,
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  UNIQUE KEY `chr_left_right` (`chr`,`left`,`right`),
  `id` bigint(20) NOT NULL AUTO_INCREMENT
) ENGINE=MyISAM;

CREATE TABLE `pk47-pk51-gene-repeat` (
  `chr` varchar(2) NOT NULL,
  `left` bigint(20) NOT NULL,
  `right` bigint(20) NOT NULL,
  `count_k51` int(11) DEFAULT '0',
  `count_p51` int(11) DEFAULT '0',
  `count_p47` int(11) DEFAULT '0',
  `count_k47` int(11) DEFAULT '0',
  `name_left` varchar(29) NOT NULL,
  `name_right` varchar(17) NOT NULL,
  UNIQUE KEY `pos_name` (`chr`,`left`,`right`,`name_left`,`name_right`)
) ENGINE=MyISAM;

INSERT INTO `pk47-pk51-gene-repeat` (
      `chr`,`left`, `right`,`count_k51`,`name_left`, `name_right`
)
SELECT 
     a.`chr`, a.`left`, a.`right`, a.`count` as `count_k51`,
     g.`name` as `name_left`, 
     r.`name` as `name_right` 
FROM `
     51k-80-80-ignore-random-noreverse` a, 
     `genes-g38-201505` g,
     `repeats-g38-201505` r 
WHERE
     a.`chr`=g.`chr` and a.`chr`=r.`chr` and 
     a.`left` < g.`right` and a.`left` > g.`left` and 
     a.`right` < r.`right` and a.`right` > r.`left` 
     on duplicate key 
     update 
       `pk47-pk51-gene-repeat`.`count_k51`=a.`count`;

# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
'1', 'SIMPLE', 'g', 'ALL', NULL, NULL, NULL, NULL, '60433', NULL
'1', 'SIMPLE', 'a', 'ref', 'chr_left_right', 'chr_left_right', '4', 'dna_homo_pairs.g.chr', '47216', 'Using index condition'
'1', 'SIMPLE', 'r', 'ALL', NULL, NULL, NULL, NULL, '5317291', 'Using where; Using join buffer (Block Nested Loop)'

# id, select_type, table, type, possible_keys, key, key_len, ref, rows, Extra
'1', 'SIMPLE', 'a', 'ALL', 'chr_left_right', NULL, NULL, NULL, '4721638', NULL
'1', 'SIMPLE', 'g', 'ref', 'chr_left_right', 'chr_left_right', '4', 'methyl_base.a.chr', '604', 'Using index condition'
'1', 'SIMPLE', 'r', 'ref', 'chr_left_right', 'chr_left_right', '5', 'methyl_base.a.chr', '53172', 'Using index condition'

INSERT INTO `pk47-pk51-gene-repeat` (
      `chr`,`left`, `right`,`count_k51`,`name_left`, `name_right`
)
SELECT 
     "7", a.`left`, a.`right`, a.`count` as `count_k51`,
     g.`name` as `name_left`, 
     r.`name` as `name_right` 
FROM `
     51k-80-80-ignore-random-noreverse-chr7` a, 
     `genes-g38-201505-chr7` g,
     `repeats-g38-201505-chr7` r 
WHERE
     a.`left` < g.`right` and a.`left` > g.`left` and 
     a.`right` < r.`right` and a.`right` > r.`left` 
     on duplicate key 
     update 
       `pk47-pk51-gene-repeat`.`count_k51`=a.`count`;

Обычные методы оптимизации, применяемые СУБД обычно берут 1 запись из первой таблицы и ищут по всей второй таблице подходящие записи. При наличии индекса это происходит довольно быстро, за log2(m), но все таки это обращения к десятку страниц индекса ... Ваш запрос при обычном Join должен выполняться в лучшем случае за O=n*(log2(m)+log2(k)) (Где n,m,k количество записей в 3х таблицах в запросе) (хотя если посмотреть статьи по оптимизации MySQL там вообще выйдет O=n*log2(m)*log2(k))

С другой стороны, ваши данные довольно специфичны (жаль СУБД не способна это оценить). В таблицах genes и repeats лежат не пересекающиеся интервалы, следовательно при сортировке по полю left записи оказываются отсортированы и по right. В таблицах с паттернами же интервалы временами пересекаются, в итоге при сортировке по left поле right иногда немного "возвращается назад", но таких ситуаций порядка 8%.

Сравнивать один отсортированный список с отсортированным же списком возможных интервалов можно гораздо проще, идя параллельно по обоим спискам одновременно, сдвигая указатель вперед в том списке, в котором записи еще меньше, чем в другом. Причем так можно проходить параллельно по более чем по 2 спискам одновременно. При этом сложность поиска оказывается O=n+m+k.

К сожалению из за того, что список паттернов не может быть одновременно отсортирован и по left и по right нам иногда надо откатывать указатель в repeats немного назад. В MySQL при работе с курсорами это невозможно, по этой причине я решил запоминать те записи, которые выпали из последовательности возрастающих right и при этом могли попасть в интервал предыдущей записи repeats (таких оказалось совсем мало 2k из 438k). Этого можно избежать помня несколько последних записей repeats, но на MySQL это будет слишком громоздко. Сохраненные записи приходится обрабатывать за второй проход, проходя по repeats отсортированной по полю right.

На основе вышеизложенного у меня вышла следующая хранимая процедура:

create table miss_rows(`left` int not null, `right` int not null,
                       `count` int not null,`name_left` varchar(100) not null);
drop procedure if exists genjoin;
delimiter $$
create procedure genjoin()
begin
 declare endOfData integer default 0;
 declare done int default 0;
 declare cnt int default 0;
 declare g_left int default 0;  -- Переменные для genes
 declare g_right int default 0;
 declare g_name varchar(100);
 declare gen_eof int default 0;  -- Признак конца genes
 declare r_left int default 0;   -- Переменные для repeats
 declare r_right int default 0;
 declare r_Oright int default 0; -- Значение правого конца предыдущей записи
 declare r_name varchar(100);
 declare rep_eof int default 0;  -- Признак конца repeats
 declare t_left int default 0;   -- Переменные рабочей таблицы
 declare t_right int default 0;
 declare t_Oright int default 0; -- Значение правого конца предыдущей записи
 declare t_count int default 0;
 declare gen_cur cursor for
  select `left`,`right`,`name` from genes order by 1,2;
 declare rep_cur cursor for
  select `left`,`right`,`name` from repeats order by 1,2;
 declare data_cur cursor for
  select `left`,`right`,`count` from t47k order by 1,2;
 declare miss_cur cursor for
  select `left`, `right`, `count`, `name_left` from miss_rows order by 2;
 declare rep2_cur cursor for
  select `left`,`right`,`name` from repeats order by 2;

 DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET endOfData=1;
 open gen_cur;
 open rep_cur;
 open data_cur;
 truncate table miss_rows;
 row: while done=0 do
    if t_Oright<t_right then set t_Oright=t_right; end if; -- Сохраняем предыдущее значение
   fetch data_cur into t_left, t_right, t_count;
   if endOfData=1 then set done=1,endOfData=0; end if;
   while t_left >= g_right and gen_eof=0 do
     fetch gen_cur into g_left, g_right, g_name;
     if endOfData=1 then set gen_eof=1,endOfData=0; end if;
   end while;
   while t_right >= r_right and rep_eof=0 do
     if r_Oright<r_right then set r_Oright=r_right; end if; -- Сохраняем предыдущее значение
     fetch rep_cur into r_left, r_right, r_name;
     if endOfData=1 then set rep_eof=1,endOfData=0; end if;
   end while;
   if t_left <= g_left or t_left >= g_right then iterate row; end if;
   if t_Oright > t_right then  -- Концы не по порядку, пересечение интервалов !
     if t_right < r_Oright then -- Мы могли попасть в предыдущую запись repeats !
       -- но пропустили ее ...
       insert into miss_rows values(t_left, t_right, t_count,g_name);
     end if;
   end if;
   if t_right <= r_left or t_right>= r_right then iterate row; end if;
   insert into `pk47-pk51-gene-repeat`
    (`left`, `right`, `count_k47`,`name_left`,`name_right`)
    values(t_left, t_right, t_count, g_name, r_name)
    on duplicate key update `count_k47`=t_count;
 end while;
 close gen_cur;
 close rep_cur;
 close data_cur;
 open rep2_cur;
 open miss_cur;
 set r_left=0,r_right=0,rep_eof=0,done=0;
 while done=0 do
   fetch miss_cur into t_left, t_right, t_count, g_name;
   if endOfData=1 then set done=1,endOfData=0; end if;
   while t_right >= r_right and rep_eof=0 do
     fetch rep2_cur into r_left, r_right, r_name;
     if endOfData=1 then set rep_eof=1,endOfData=0; end if;
   end while;
   if t_right > r_left then
     insert into `pk47-pk51-gene-repeat`
      (`left`, `right`, `count_k47`,`name_left`,`name_right`)
      values(t_left, t_right, t_count, g_name, r_name)
      on duplicate key update `count_k47`=t_count;
   end if;
 end while;
end$$

Время выполнения данной процедуры на контрольном примере из 6k / 444k / 438k записей, составило 45 секунд ... Правда в контрольном примере не было поля chr, когда оно есть, оно конечно должно так же участвовать в сортировке списков и сравниваться во всех условиях. Таблицы 47, 51 и т.п. стоит обрабатывать за отдельные проходы. Если бы была возможность возвращаться назад, можно было бы попробовать обработать все за один проход. Аналогичный алгоритм на perl, беря данные из отсортированных csv файлов работает 4 секунды...

В связи с тем, что в repeats все таки могут быть пересечения интервалов, все немного сложнее и для получения всех вариантов (коих примерно на 200 записей больше изначального варианта (в 130k записей)) лучше все таки использовать массивы, что бы можно было заглядывать немного вперед. на perl это выглядит так

Классная тема вопроса! Только на чистом SQL такую процедуру смерти-подобно делать. Гораздо проще скрипт (например PHP) накатать - который пусть и дольше, но всё выполнит - не единым INSERT-SELECT-ом, а вставляя по одиночке, или в транзакциях пачками. Так понимаю проблема не в производительности, а в том что выполнить до конца не удаётся в принципе. То лучше есть менять подход.

То есть советую из таблицы 51k-80-80-ignore-random-noreverse выбирать для каждой её записи сопоставления по таблицам genes-g38-201505 и repeats-g38-201505. Если сопоставление найдено, то проводить INSERT.

Лимитировать можно и без скрипта. Для начала - чтобы выбирать по одной записи - таблице 51k-80-80-ignore-random-noreverse нужно добавить первичный ключ:

ALTER TABLE `51k-80-80-ignore-random-noreverse` ADD PRIMARY KEY (id);

А дальше вашим же запросом теперь можно брать любой отрезок по таблице 51k-80-80-ignore-random-noreverse:

SELECT 
     a.`chr`, a.`left`, a.`right`, a.`count` as `count_k51`,
     g.`name` as `name_left`, 
     r.`name` as `name_right` 
FROM `
     51k-80-80-ignore-random-noreverse a, 
     `genes-g38-201505` g,
     `repeats-g38-201505` r 
WHERE
     a.id > 3000 and a.id < 4000 and /* <- лимитирование обрабатываемых данных */
     a.`chr`=g.`chr` and a.`chr`=r.`chr` and 
     a.`left` < g.`right` and a.`left` > g.`left` and 
     a.`right` < r.`right` and a.`right` > r.`left`;

И не будет такой ситуации как вы написали limit 0,1000; limit 1001,2000 и так далее, как я понимаю, использовать бесполезно, поскольку каждый следующий этап сервер всё равно будет проходить предыдущие так как теперь работает индекс id.

Второе - нужны правильные индексы специально под запрос:

ALTER TABLE `51k-80-80-ignore-random-noreverse` ADD KEY `BASE` (`chr`, `left`, `right`);
ALTER TABLE `genes-g38-201505` ADD KEY `BASE` (`chr`, `left`, `right`);
ALTER TABLE `repeats-g38-201505` ADD KEY `BASE` (`chr`, left`, `right`);

Индексы под запрос создавать очень просто - нужно добавить в индексы все столбцы(по одному мульти-индексу на таблицу), которые участвуют в WHERE кроме первичного ключа, порядок: от столбца с наименьшим кол-вом вариантов, к наибольшему чтобы оптимально работал BTREE.

Ну и последнее - вы используете SQL для очень нетипичной задачи, последний раз видел когда перемножают таблицы в рабочем коде (делают JOIN не оператором = ) 5 лет назад: подсказать что-то, что попадёт в яблочко сложно, кроме того что бить один "толстый" запрос на выполнение множества более "тонких" запросов. Совсем в яблочко - это проверять совпадения в цикле скрипта, который подключается к SQL - но очевидно нужно уметь сделать и запустить такой скрипт, либо обращаться к фрилансерам.

Начал с рассмотрения запроса, который приведён в тексте вопроса. Несмотря на годный с виду EXPLAIN PLAN, запрос работает так медленно, будто читает всю таблицу вместо обращений к индексу. Правка индексов мало что меняет.

Потом пришло понимание вот какого момента: при обращении к таблице генов, мы указываем только одну границу для колонки g.left, а именно: a.left > g.left. Таким образом, мы требуем, чтобы граница g.left у гена была меньше, чем граница a.left у эксперимента. И всё. А если граница a.left у эксперимента имеет большое значение, например 100 миллионов? Ну да, чтобы обработать одну строку таблицы эксперимента, понадобится прочитать почти всю таблицу генов. Так уж устроены индексы, что если нет знака равенства, то дело плохо. Таблица генов – относительно невелика, но данные рассуждения относятся и к работе с весьма большой таблицей повторов: a.right > r.left. И это главная проблема запроса.

Предоставленный автором фрагмент реальных данных показал, что для генов и повторов разница между left и right в одной строке может достигать 1,5 млн и 0,5 млн. Это слишком много, но я всё же попробовал оптимизировать условия запроса, и никакого выигрыша не получил. Почему-то даже наоборот.

Как уже было упомянуто другими участниками, гены, повторы и эксперименты можно рассматривать в качестве диапазонов на оси координат, от left до right. Однако, это только половина дела :) Можно же и саму ось координат тоже поделить на диапазоны.
Поделив ось на диапазоны, мы можем затем для каждого из этих диапазонов найти все гены и повторы, которые туда попадают хотя бы одним краем.

Пара примерчиков
Пример 1:
Ген с координатами left=10, right=50.
Ось делится на диапазоны по 100 единиц, т.е. 0-99, 100-199, 200-299, ...
В данном случае ген попадает только на один диапазон: 0-99.
Пример 2:
Ген с координатами left=140, right=360.
Ось делится на диапазоны так же: 0-99, 100-199, 200-299, 300-399, ...
В данном случае ген попадает в три диапазона: 100-199, 200-299, 300-399. Первый и третий диапазоны ген покрывает частично, а второй (средний) – полностью.

Зачем нужна эта возня с диапазонами на оси координат?

Чтобы понять, давайте вернёмся к условиям задачи: найти эксперименты, которые левым краем left попадают на ген, а правым краем right попадают на повтор.

Изначально мы искали попадание на гены и повторы, используя неравенства в условиях проверки, и при этом SQL-сервер просматривал гораздо больше строк в таблицах, чем хотелось бы.

Теперь же, зная в какой диапазон оси координат попадает значение левой границы эксперимента a.left, мы могли бы сразу проверить именно этот диапазон на наличие в нём (в диапазоне) каких-либо генов. Аналогично с повторами, только для них проверяем по правой границе a.right.

Есть ещё одна тонкость: может оказаться так, что ген лежит в координатах left=10, right=20, а эксперимент в координатах left=25, right=45. В таком случае они лежат в одном диапазоне 0-99, но они не удовлетворяют условиям задачи. Получается, что проверив диапазон, мы сделали только предварительную выборку более-менее подходящих генов, но для полной уверенности надо применить условия из первоначального запроса.
В итоге, если ось координат разбить по десяткам тысяч, и пронумеровать диапазоны, деля координату на 10000, то запрос для решения задачи будет выглядеть как-то так:

SELECT
    a.`chr`, a.`left`, a.`right`, a.`count` as `count_k51`,
    sg.`name` as `name_left`,
    sr.`name` as `name_right`
FROM
    `51k-80-80-ignore-random-noreverse` a
    INNER JOIN `unwind-genes` g ON  /* гены по диапазонам */
        ( a.`chr` = g.`chr` ) and  /* хромосома */
        ( a.`left` div 10000 = g.`hkey` ) and  /* диапазон */
        ( g.`left` < a.`left` ) and ( a.`left` < g.`right` )  /* точное выполнение условий */
    INNER JOIN `unwind-repeats` r ON  /* повторы по диапазонам */
        ( a.`chr` = r.`chr` ) and  /* хромосома */
        ( a.`right` div 10000 = r.`hkey` ) and  /* диапазон */
        ( r.`left` < a.`right` ) and ( a.`right` < r.`right` )  /* точное выполнение условий */
    INNER JOIN `genes-g38-201505` sg ON
        ( g.`gene_id` = sg.`id` )  /* взять имя гена */
    INNER JOIN `repeats-g38-201505` sr ON
        ( r.`repeat_id` = sr.`id` )  /* взять имя повтора */

В данном запросе таблицы unwind-genes и unwind-repeats содержат сведения обо всех диапазонах оси координат, поделённой на отрезки в 10000 единиц, куда попадают, соответственно гены и повторы.

Прикладываю скрипт на обе таблицы, и на хранимые процедуры для их заполнения.

CREATE TABLE `unwind-genes`
(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `chr` varchar(2) NOT NULL,
  `hkey` int(11) NOT NULL,
  `gene_id` int(11) NOT NULL,
  `left` int(11) NOT NULL,
  `right` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX (`chr`, `hkey`, `gene_id`)
) ENGINE=MyISAM;

CREATE TABLE `unwind-repeats`
(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `chr` varchar(2) NOT NULL,
  `hkey` int(11) NOT NULL,
  `repeat_id` int(11) NOT NULL,
  `left` int(11) NOT NULL,
  `right` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX (`chr`, `hkey`, `repeat_id`)
) ENGINE=MyISAM;

DELIMITER $$
CREATE PROCEDURE `proc_unwind_genes`()
BEGIN
    DECLARE fetched INT DEFAULT TRUE;
    DECLARE v_chr VARCHAR(2);
    DECLARE v_left INT;
    DECLARE v_right INT;
    DECLARE v_id INT;
    DECLARE hkey INT;
    DECLARE hkey_max INT;
    DECLARE cur_genes CURSOR FOR
        SELECT `chr`, `left`, `right`, `id`
        FROM `genes-g38-201505`;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = FALSE;
    TRUNCATE TABLE `unwind-genes`;
    OPEN cur_genes;
    FETCH cur_genes INTO v_chr, v_left, v_right, v_id;
    WHILE (fetched) DO
        SET hkey = v_left div 10000;
        SET hkey_max = v_right div 10000;
        WHILE (hkey <= hkey_max) DO
            INSERT INTO `unwind-genes`
            ( `chr`, `hkey`, `left`, `right`, `gene_id` )
            VALUES
            ( v_chr, hkey, v_left, v_right, v_id );
            SET hkey = hkey + 1;
        END WHILE;
        FETCH cur_genes INTO v_chr, v_left, v_right, v_id;
    END WHILE;
    CLOSE cur_genes;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `proc_unwind_repeats`()
BEGIN
    DECLARE fetched INT DEFAULT TRUE;
    DECLARE v_chr VARCHAR(2);
    DECLARE v_left INT;
    DECLARE v_right INT;
    DECLARE v_id INT;
    DECLARE hkey INT;
    DECLARE hkey_max INT;
    DECLARE cur_repeats CURSOR FOR
        SELECT `chr`, `left`, `right`, `id`
    FROM `repeats-g38-201505`;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = FALSE;
    TRUNCATE TABLE `unwind-repeats`;
    OPEN cur_repeats;
    FETCH cur_repeats INTO v_chr, v_left, v_right, v_id;
    WHILE (fetched) DO
        SET hkey = v_left div 10000;
        SET hkey_max = v_right div 10000;
        WHILE (hkey <= hkey_max) DO
            INSERT INTO `unwind-repeats`
            ( `chr`, `hkey`, `left`, `right`, `repeat_id` )
            VALUES
            ( v_chr, hkey, v_left, v_right, v_id );
            SET hkey = hkey + 1;
        END WHILE;
        FETCH cur_repeats INTO v_chr, v_left, v_right, v_id;
    END WHILE;
    CLOSE cur_repeats;
END$$
DELIMITER ;

Время выполнения на моем компьютере (под виртуальной машиной) на полученном фрагменте реальных данных:

CALL `proc_unwind_genes`();  /* ~ 0.25 мс */<br>
CALL `proc_unwind_repeats`();   /* ~ 15 сек. */<br>
INSERT ... SELECT ...; /* ~ 2*15 сек. */<br>

Хочу отдельно упомянуть, что вызов хранимых процедур (для формирования промежуточных данных по диапазонам) нужен только однократный. Вызывать их повторно нет нужды до тех пор, пока не изменятся служебные данные по генам/повторам.

Плюсы данного метода решения:
- высокая скорость
- чистый SQL, без использования дополнительных инструментов
- если таблицы генов и повторов не меняются, то промежуточные данные не нужно пересчитывать для каждого нового эксперимента - экономия времени обработки для каждого нового эксперимента
Минусы:
- база увеличивается в размерах из-за хранения промежуточных данных по диапазонам

Возможные пути для дальнейшей оптимизации решения:
- разбивка оси координат по диапазонам другого размера; при уменьшении размера диапазонов можно получить ускорение в выполнении запроса, но увеличится объём промежуточных данных, их объём может стать в десятки раз больше объёма исходных таблиц
- можно попробовать в таблицы с промежуточными данными закинуть имена генов и повторов, чтобы избавиться от соединения с исходными таблицами (вместо 5-и таблиц запрос будет обходиться 3-мя)

На всякий случай, оставляю полный скрипт своей тестовой БД

CREATE SCHEMA `test` DEFAULT COLLATE utf8_bin;

USE `test`;

CREATE TABLE `genes-g38-201505`
(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `chr` varchar(2) NOT NULL,
  `left` int(11) NOT NULL,
  `right` int(11) NOT NULL,
  `Complement` int(11) NOT NULL,
  `Name` tinytext NOT NULL,
  `source` tinytext NOT NULL,
  `ENSEMBL` tinytext NOT NULL,
  `gene_version` tinytext NOT NULL,
  `gene_name` tinytext NOT NULL,
  `gene_source` tinytext NOT NULL,
  `gene_biotypeid` tinytext NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `ix_chr_left` (`chr`, `left`)
) ENGINE=MyISAM;

CREATE TABLE `repeats-g38-201505`
(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `chr` varchar(2) DEFAULT NULL,
  `left` int(11) DEFAULT NULL,
  `right` int(11) DEFAULT NULL,
  `name` tinytext,
  PRIMARY KEY (`id`),
  INDEX `ix_chr_left` (`chr`, `left`)
) ENGINE=MyISAM;

CREATE TABLE `47k-80-80-ignore-random-noreverse`
(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `chr` varchar(2) NOT NULL,
  `left` int(11) NOT NULL,
  `right` int(11) NOT NULL,
  `count` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `chr_left` (`chr`, `left`)
) ENGINE=MyISAM;

CREATE TABLE `51k-80-80-ignore-random-noreverse`
(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `chr` varchar(2) NOT NULL,
  `left` int(11) NOT NULL,
  `right` int(11) NOT NULL,
  `count` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `chr_left` (`chr`, `left`)
) ENGINE=MyISAM;

CREATE TABLE `pk47-pk51-gene-repeat`
(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `chr` varchar(2) NOT NULL,
  `left` int(11) NOT NULL,
  `right` int(11) NOT NULL,
  `count_k51` int(11) DEFAULT '0',
  `count_p51` int(11) DEFAULT '0',
  `count_p47` int(11) DEFAULT '0',
  `count_k47` int(11) DEFAULT '0',
  `name_left` varchar(30) NOT NULL,
  `name_right` varchar(30) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `pos_name` (`chr`, `left`, `right`, `name_left`, `name_right`),
  INDEX `ix_compare` (`chr`, `left`, `right`, `count_k47`, `count_k51`, `name_left`, `name_right`)
) ENGINE=MyISAM;

CREATE TABLE `pk47-pk51-gene-repeat-compare`
(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `chr` varchar(2) NOT NULL,
  `left` int(11) NOT NULL,
  `right` int(11) NOT NULL,
  `count_k51` int(11) DEFAULT '0',
  `count_p51` int(11) DEFAULT '0',
  `count_p47` int(11) DEFAULT '0',
  `count_k47` int(11) DEFAULT '0',
  `name_left` varchar(30) NOT NULL,
  `name_right` varchar(30) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX `pos_name` (`chr`, `left`, `right`, `name_left`, `name_right`),
  INDEX `ix_compare` (`chr`, `left`, `right`, `count_k47`, `count_k51`, `name_left`, `name_right`)
) ENGINE=MyISAM;

CREATE TABLE `unwind-genes`
(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `chr` varchar(2) NOT NULL,
  `hkey` int(11) NOT NULL,
  `gene_id` int(11) NOT NULL,
  `left` int(11) NOT NULL,
  `right` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX (`chr`, `hkey`, `gene_id`)
) ENGINE=MyISAM;

CREATE TABLE `unwind-repeats`
(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `chr` varchar(2) NOT NULL,
  `hkey` int(11) NOT NULL,
  `repeat_id` int(11) NOT NULL,
  `left` int(11) NOT NULL,
  `right` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE INDEX (`chr`, `hkey`, `repeat_id`)
) ENGINE=MyISAM;

DELIMITER $$
CREATE PROCEDURE `proc_unwind_genes`()
BEGIN
    DECLARE fetched INT DEFAULT TRUE;
    DECLARE v_chr VARCHAR(2);
    DECLARE v_left INT;
    DECLARE v_right INT;
    DECLARE v_id INT;
    DECLARE hkey INT;
    DECLARE hkey_max INT;
    DECLARE cur_genes CURSOR FOR
        SELECT `chr`, `left`, `right`, `id`
        FROM `genes-g38-201505`;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = FALSE;
    TRUNCATE TABLE `unwind-genes`;
    OPEN cur_genes;
    FETCH cur_genes INTO v_chr, v_left, v_right, v_id;
    WHILE (fetched) DO
        SET hkey = v_left div 10000;
        SET hkey_max = v_right div 10000;
        WHILE (hkey <= hkey_max) DO
            INSERT INTO `unwind-genes`
            ( `chr`, `hkey`, `left`, `right`, `gene_id` )
            VALUES
            ( v_chr, hkey, v_left, v_right, v_id );
            SET hkey = hkey + 1;
        END WHILE;
        FETCH cur_genes INTO v_chr, v_left, v_right, v_id;
    END WHILE;
    CLOSE cur_genes;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `proc_unwind_repeats`()
BEGIN
    DECLARE fetched INT DEFAULT TRUE;
    DECLARE v_chr VARCHAR(2);
    DECLARE v_left INT;
    DECLARE v_right INT;
    DECLARE v_id INT;
    DECLARE hkey INT;
    DECLARE hkey_max INT;
    DECLARE cur_repeats CURSOR FOR
        SELECT `chr`, `left`, `right`, `id`
    FROM `repeats-g38-201505`;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET fetched = FALSE;
    TRUNCATE TABLE `unwind-repeats`;
    OPEN cur_repeats;
    FETCH cur_repeats INTO v_chr, v_left, v_right, v_id;
    WHILE (fetched) DO
        SET hkey = v_left div 10000;
        SET hkey_max = v_right div 10000;
        WHILE (hkey <= hkey_max) DO
            INSERT INTO `unwind-repeats`
            ( `chr`, `hkey`, `left`, `right`, `repeat_id` )
            VALUES
            ( v_chr, hkey, v_left, v_right, v_id );
            SET hkey = hkey + 1;
        END WHILE;
        FETCH cur_repeats INTO v_chr, v_left, v_right, v_id;
    END WHILE;
    CLOSE cur_repeats;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `run_query_authors`()
BEGIN
    TRUNCATE TABLE `pk47-pk51-gene-repeat`;
    INSERT INTO `pk47-pk51-gene-repeat`
    ( `chr`, `left`, `right`, `count_k47`, `name_left`, `name_right` )
    SELECT
        a.`chr`, a.`left`, a.`right`, a.`count` as `count_k47`,
        g.`name` as `name_left`,
        r.`name` as `name_right`
    FROM
        `47k-80-80-ignore-random-noreverse` a,
        `genes-g38-201505` g,
        `repeats-g38-201505` r
    WHERE
        a.`chr`=g.`chr` and a.`chr`=r.`chr` and
        a.`left` < g.`right` and a.`left` > g.`left` and
        a.`right` < r.`right` and a.`right` > r.`left` and
        ( a.`id` between 10000 and 10100 )
    ON DUPLICATE KEY
    UPDATE `pk47-pk51-gene-repeat`.`count_k47`=a.`count`;
    INSERT INTO `pk47-pk51-gene-repeat`
    ( `chr`, `left`, `right`, `count_k51`, `name_left`, `name_right` )
    SELECT
        a.`chr`, a.`left`, a.`right`, a.`count` as `count_k51`,
        g.`name` as `name_left`,
        r.`name` as `name_right`
    FROM
        `51k-80-80-ignore-random-noreverse` a,
        `genes-g38-201505` g,
        `repeats-g38-201505` r
    WHERE
        a.`chr`=g.`chr` and a.`chr`=r.`chr` and
        a.`left` < g.`right` and a.`left` > g.`left` and
        a.`right` < r.`right` and a.`right` > r.`left` and
        ( a.`id` between 10000 and 10100 )
    ON DUPLICATE KEY
    UPDATE `pk47-pk51-gene-repeat`.`count_k51`=a.`count`;
END$$
DELIMITER ;

DELIMITER $$
CREATE PROCEDURE `run_query_hkeys`()
BEGIN
    TRUNCATE TABLE `pk47-pk51-gene-repeat-compare`;
    INSERT INTO `pk47-pk51-gene-repeat-compare`
    ( `chr`, `left`, `right`, `count_k47`, `name_left`, `name_right` )
    SELECT
        a.`chr`, a.`left`, a.`right`, a.`count` as `count_k47`,
        sg.`name` as `name_left`,
        sr.`name` as `name_right`
    FROM
        `47k-80-80-ignore-random-noreverse` a
        INNER JOIN `unwind-genes` g ON
            ( a.`chr` = g.`chr` ) and
            ( a.`left` div 10000 = g.`hkey` ) and
            ( g.`left` < a.`left` ) and ( a.`left` < g.`right` )
        INNER JOIN `unwind-repeats` r ON
            ( a.`chr` = r.`chr` ) and
            ( a.`right` div 10000 = r.`hkey` ) and
            ( r.`left` < a.`right` ) and ( a.`right` < r.`right` )
        INNER JOIN `genes-g38-201505` sg ON
            ( g.`gene_id` = sg.`id` )
        INNER JOIN `repeats-g38-201505` sr ON
            ( r.`repeat_id` = sr.`id` )
    WHERE
        ( a.`id` between 10000 and 10100 )
    ON DUPLICATE KEY
    UPDATE `pk47-pk51-gene-repeat-compare`.`count_k47` = a.`count`;
    INSERT INTO `pk47-pk51-gene-repeat-compare`
    ( `chr`, `left`, `right`, `count_k51`, `name_left`, `name_right` )
    SELECT
        a.`chr`, a.`left`, a.`right`, a.`count` as `count_k51`,
        sg.`name` as `name_left`,
        sr.`name` as `name_right`
    FROM
        `51k-80-80-ignore-random-noreverse` a
        INNER JOIN `unwind-genes` g ON
            ( a.`chr` = g.`chr` ) and
            ( a.`left` div 10000 = g.`hkey` ) and
            ( g.`left` < a.`left` ) and ( a.`left` < g.`right` )
        INNER JOIN `unwind-repeats` r ON
            ( a.`chr` = r.`chr` ) and
            ( a.`right` div 10000 = r.`hkey` ) and
            ( r.`left` < a.`right` ) and ( a.`right` < r.`right` )
        INNER JOIN `genes-g38-201505` sg ON
            ( g.`gene_id` = sg.`id` )
        INNER JOIN `repeats-g38-201505` sr ON
            ( r.`repeat_id` = sr.`id` )
    WHERE
        ( a.`id` between 10000 and 10100 )
    ON DUPLICATE KEY
    UPDATE `pk47-pk51-gene-repeat-compare`.`count_k51` = a.`count`;
END$$
DELIMITER ;

DELIMITER $$
CREATE FUNCTION `check_validity`() RETURNS varchar(50) CHARSET utf8
BEGIN
    DECLARE cnt_1 INT;
    DECLARE cnt_2 INT;
    DECLARE cnt_3 INT;
    DECLARE cnt_4 INT;
    DECLARE cnt_5 INT;
    SET cnt_1 = (SELECT COUNT(*) FROM `pk47-pk51-gene-repeat`);
    SET cnt_2 = (SELECT COUNT(*) FROM `pk47-pk51-gene-repeat-compare`);
    IF (cnt_1 != cnt_2) THEN
        RETURN 'Error 1';
    END IF;
    SET cnt_3 =
    (
        SELECT COUNT(*)
        FROM
            `pk47-pk51-gene-repeat` a
            LEFT JOIN `pk47-pk51-gene-repeat-compare` b ON
                ( a.`chr` = b.`chr` ) AND
                ( a.`left` = b.`left` ) AND
                ( a.`right` = b.`right` ) AND
                ( a.`count_k47` = b.`count_k47` ) AND
                ( a.`count_k51` = b.`count_k51` ) AND
                ( a.`name_left` = b.`name_left` ) AND
                ( a.`name_right` = b.`name_right` )
        WHERE
            ( b.`chr` IS NULL )
    );
    IF (cnt_3 > 0) THEN
        RETURN 'Error 2';
    END IF;
    SET cnt_4 =
    (
        SELECT COUNT(*)
        FROM
            `pk47-pk51-gene-repeat-compare` a
            LEFT JOIN `pk47-pk51-gene-repeat` b ON
                ( a.`chr` = b.`chr` ) AND
                ( a.`left` = b.`left` ) AND
                ( a.`right` = b.`right` ) AND
                ( a.`count_k47` = b.`count_k47` ) AND
                ( a.`count_k51` = b.`count_k51` ) AND
                ( a.`name_left` = b.`name_left` ) AND
                ( a.`name_right` = b.`name_right` )
        WHERE
            ( b.`chr` IS NULL )
    );
    IF (cnt_4 > 0) THEN
        RETURN 'Error 3';
    END IF;
    SET cnt_5 =
    (
        SELECT COUNT(*)
        FROM
            `pk47-pk51-gene-repeat-compare` a
            INNER JOIN `pk47-pk51-gene-repeat` b ON
                ( a.`chr` = b.`chr` ) AND
                ( a.`left` = b.`left` ) AND
                ( a.`right` = b.`right` ) AND
                ( a.`count_k47` = b.`count_k47` ) AND
                ( a.`count_k51` = b.`count_k51` ) AND
                ( a.`name_left` = b.`name_left` ) AND
                ( a.`name_right` = b.`name_right` )
    );
    IF (cnt_5 != cnt_1) THEN
        RETURN 'Error 4';
    END IF;
    RETURN Concat('Success! (',
        convert(cnt_1, char(10)), ';',
        convert(cnt_2, char(10)), ';',
        convert(cnt_3, char(10)), ';',
        convert(cnt_4, char(10)), ';',
        convert(cnt_5, char(10)), ')');
END$$
DELIMITER ;

P.S.:
Признаться, раньше с MySQL дела не имел, работал с данной СУБД впервые на данной задаче.
Благодарю, что дали повод и с MySQL ознакомиться, и поучаствовать в решении интересной реальной задачи :)
И прошу прощения за много буков. Не нашёл, как сделать сворачиваемые куски текста (прятать под спойлер)