MySQL query optimization with between or larger than > condition

Question

Problem: slow query.

• table1 has about 5 000 rows
• table2 has about 50 000 rows
• timestamp format is int(11)
• MySQL - 20 seconds (with indexes)

• PostgreSQL - 0,04 seconds (with indexes)

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

Can anybody help me with optimize this query for MySQL?

Explain:

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

Tables:

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

A couple of points spring to mind but both feel like long-shots. Realistically it looks as though there shouldn't be much you can do to your query assuming your example is an accurate representation.

1 : You are using BIGINT which has a maximum value of 9x10^18 (SIGNED). INT has a max value of 4x10^9 (UNSIGNED), compared to days timestamp which is around 1.4x10^9 (all values approximate) and so consider changing the data type of that column in both tables from BIGINT to INT UNSIGNED or DATETIME

2 : The ROW_FORMAT is COMPACT which may cause issues with BTREE indexes (source). You are dealing with INT data types and so a ROW_FORMAT of FIXED would suffice so try changing to ROW_FORMAT=FIXED on both tables

3 : If always expecting rows to be returned from table2 for table1 rows then INNER JOIN would be more efficient than LEFT JOIN