# Index help, range condition

**URL:** <https://forums.percona.com/t/index-help-range-condition/1350>\
**Category:** Other MySQL® Questions\
**Created:** [February 16, 2010, 5:28am UTC](https://forums.percona.com/t/index-help-range-condition/1350 "2010-02-16T05:28:37Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![dahuuhad](https://avatars.discourse-cdn.com/v4/letter/d/8e7dd6/32.png) [@dahuuhad](https://forums.percona.com/u/dahuuhad)\
**Post date:** [February 16, 2010, 5:28am UTC](https://forums.percona.com/t/index-help-range-condition/1350/1 "2010-02-16T05:28:37Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\
**Post date:** [February 16, 2010, 5:48am UTC](https://forums.percona.com/t/index-help-range-condition/1350/2 "2010-02-16T05:48:30Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![dahuuhad](https://avatars.discourse-cdn.com/v4/letter/d/8e7dd6/32.png) [@dahuuhad](https://forums.percona.com/u/dahuuhad)\
**Post date:** [February 16, 2010, 6:18am UTC](https://forums.percona.com/t/index-help-range-condition/1350/3 "2010-02-16T06:18:33Z")

</div>

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)