Оптимизация запросов MySQL с условием между или большим, чем>

Question

Проблема: медленный запрос.

• table1 имеет около 5 000 строк
• table2 имеет около 50 000 строк
• формат отметки времени int(11)
• MySQL - 20 секунд (с индексами)

• PostgreSQL - 0,04 секунды (с индексами)

  SELECT * 
  FROM table1
    LEFT JOIN table2 
      ON table2_timestamp BETWEEN table1_timestamp - 500 
                              AND table1_timestamp + 500 ;
  

Кто-нибудь может мне помочь с оптимизацией этого запроса для MySQL?

Объясните:

1   SIMPLE  a   index       a   9       2   Using index
1   SIMPLE  b   index   b   b   9       5   Using index

Таблицы:

CREATE TABLE `a` (
  `id`  int(11) NOT NULL AUTO_INCREMENT ,
  `table1_timestamp`  bigint(20) NULL DEFAULT NULL ,
  PRIMARY KEY (`id`),
  INDEX `a` (`table1_timestamp`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=3
ROW_FORMAT=COMPACT
;

CREATE TABLE `b` (
  `id`  int(11) NOT NULL AUTO_INCREMENT ,
  `table2_timestamp`  bigint(20) NULL DEFAULT NULL ,
  PRIMARY KEY (`id`),
  INDEX `a` (`table2_timestamp`) USING BTREE 
)
ENGINE=InnoDB
DEFAULT CHARACTER SET=utf8 COLLATE=utf8_general_ci
AUTO_INCREMENT=3
ROW_FORMAT=COMPACT
;

Answer 1

На ум приходят пара моментов, но оба ощущаются как длинные удары. На самом деле это выглядит так, как будто вы ничего не можете сделать со своим запросом, предполагая, что ваш пример является точным представлением.

1: Вы используете BIGINT с максимальным значением 9x10^18 (ПОДПИСАНО). Максимальное значение INT равно 4x10^9 (UNSIGNED) по сравнению с отметкой времени в днях, которая составляет около 1,4x10 ^ 9 (все значения приблизительны), поэтому рассмотрите возможность изменения типа данных этого столбца в обеих таблицах с BIGINT в INT UNSIGNED или же DATETIME

2: ROW_FORMAT является КОМПАКТНЫМ, что может вызвать проблемы с индексами BTREE ( источник). Вы имеете дело с типами данных INT, и поэтому достаточно ROW_FORMAT из FIXED, поэтому попробуйте изменить на ROW_FORMAT=FIXED на обоих столах

3: Если всегда ожидать, что строки будут возвращены из таблицы2 для строк таблицы1, тогда INNER JOIN будет более эффективным, чем LEFT JOIN