GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Index help, range condition

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Index help, range condition

    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.

    Comment


    • #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)

      Comment

      Working...
      X