Index help, range condition

MySQL & MariaDB Other MySQL® Questions
1

I have a table (about 3 million rows) that consists of an ID and two columns, start and end.

Which index should I use to optimize the following query? Is it better to not have any index at all and always perform a full table scan?

SET @pStart:=695635200;
SET @pEnd:=695764000;

SELECT id, start, end FROM t1
WHERE start < @pEnd AND end > @pStart
ORDER BY start, end;

No indexes (261 rows in set (0,43 sec))

CREATE TABLE t1 (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
start INT UNSIGNED NOT NULL,
end INT UNSIGNED NOT NULL) ENGINE=MYISAM;

DESC
SELECT id, start, end FROM t1
WHERE start < @pEnd AND end > @pStart
ORDER BY start, end;
±—±------------±------±-----±--------------±-----±- -------±-----±--------±----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±------±-----±--------------±-----±- -------±-----±--------±----------------------------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 2799815 | Using where; Using filesort |
±—±------------±------±-----±--------------±-----±- -------±-----±--------±----------------------------+
1 row in set (0,00 sec)

Index on both start and end (261 rows in set (0,92 sec))

CREATE TABLE t2 (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
start INT UNSIGNED NOT NULL,
end INT UNSIGNED NOT NULL,
KEY (start),
KEY (end));

DESC
SELECT id, start, end FROM t2
WHERE start < @pEnd AND end > @pStart
ORDER BY start, end;
±—±------------±------±------±--------------±-----± --------±-----±-------±----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±------±------±--------------±-----± --------±-----±-------±----------------------------+
| 1 | SIMPLE | t2 | range | start,end | end | 5 | NULL | 331296 | Using where; Using filesort |
±—±------------±------±------±--------------±-----± --------±-----±-------±----------------------------+
1 row in set (0,00 sec)

A combined index (261 rows in set (0,42 sec))

CREATE TABLE t3 (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
start INT UNSIGNED NOT NULL,
end INT UNSIGNED NOT NULL,
KEY (start,end));

DESC
SELECT id, start, end FROM t3
WHERE start < @pEnd AND end > @pStart
ORDER BY start, end;
±—±------------±------±-----±--------------±-----±- -------±-----±--------±----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±------±-----±--------------±-----±- -------±-----±--------±----------------------------+
| 1 | SIMPLE | t3 | ALL | start | NULL | NULL | NULL | 2799815 | Using where; Using filesort |
±—±------------±------±-----±--------------±-----±- -------±-----±--------±----------------------------+
1 row in set (0,00 sec)

Thanks,
Daniel

2

CREATE TABLE t2 (
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
start INT UNSIGNED NOT NULL,
end INT UNSIGNED NOT NULL,
KEY (start),
KEY (end));

SELECT id, start, end FROM t2
WHERE (start BETWEEN @pStart AND @pEnd) OR (end BETWEEN @pStart AND @pEnd)
ORDER BY start, end;

If this does not use index merge, try to use UNION for the resultset of the start and end clause seperately.

3

Thanks, this is much better

The query now takes about 0,01 sec instead of ~0,43 sec

DESC
SELECT id, start, end FROM t2
WHERE (start BETWEEN @pStart AND @pEnd-1) OR (end BETWEEN @pStart+1 AND @pEnd)
ORDER BY start, end;

±—±------------±------±------------±--------------±- ---------±--------±-----±-----±------------------------- --------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
±—±------------±------±------------±--------------±- ---------±--------±-----±-----±------------------------- --------------------------------+
| 1 | SIMPLE | t2 | index_merge | start,end | start,end | 5,5 | NULL | 449 | Using sort_union(start,end); Using where; Using filesort |
±—±------------±------±------------±--------------±- ---------±--------±-----±-----±------------------------- --------------------------------+
1 row in set (0,00 sec)