Optimize the query by selecting a period

Question

Given the following table:

Table events
id
start_time
end_time

Is there a way to quickly find a constant?

eg.

SELECT *
FROM events
WHERE start_time<='2009-02-18 16:27:12' 
AND     end_time>='2009-02-18 16:27:12'

I am using MySQL. The presence of an index in any field should still check the range. In addition, the index in both fields will not matter (only the first will be used).

I can add fields / indexes to the table (so adding an indexed constructed field containing information to both fields would be acceptable).

PS The need for this came from this question: Optimize SQL, which uses clause clause

Answer 1

There is one caveat in my solution:

1) , MyISAM . MyISAM, , MyISAM .

, , , :

MySQL (. ). , MyISAM R-Tree (. ), . , , .

, , GeoIP. , , .

, / : , 0,0 xy, , , , , . , . , .

:

1) MyISAM ( , , , , MyISAM).

alter table events engine = MyISAM;

2) , . , .

alter table events add column time_poly polygon NOT NULL;

3) ( , , , , , ). , unix_timestamp (. , ).

update events set time_poly := LINESTRINGFROMWKB(LINESTRING(
    POINT(unix_timestamp(start_time), -1),
    POINT(unix_timestamp(end_time), -1),
    POINT(unix_timestamp(end_time), 1),
    POINT(unix_timestamp(start_time), 1),
    POINT(unix_timestamp(start_time), -1)
  ));

4) ( , MyISAM "ERROR 1464 (HY000): SPATIAL" ).

alter table events add SPATIAL KEY `IXs_time_poly` (`time_poly`);

5) , .

SELECT * 
FROM events force index (IXs_time_poly)
WHERE MBRCONTAINS(events.time_poly, POINTFROMWKB(POINT(unix_timestamp('2009-02-18 16:27:12'), 0)));

100% , MySQL . , select - :

mysql> explain SELECT *
    -> FROM events force index (IXs_time_poly)
    -> on MBRCONTAINS(events.time_poly, POINTFROMWKB(POINT(unix_timestamp('2009-02-18 16:27:12'), 0)));
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key           | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
|  1 | SIMPLE      | B     | range | IXs_time_poly | IXs_time_poly | 32      | NULL |    1 | Using where | 
+----+-------------+-------+-------+---------------+---------------+---------+------+------+-------------+
1 row in set (0.00 sec)

, inter.

, - .

,

-Dipin

Answer 2

MySQL.

, start_time <= const ORDER BY start_time DESC LIMIT 1 end_time >= const.

, MySQL - INDEX RANGE SCAN ORDER BY , .

CREATE UNIQUE INDEX ux_b_start ON b (start_date);

CREATE FUNCTION `fn_get_last_b`(event_date TIMESTAMP) RETURNS int(11)
BEGIN
  DECLARE id INT;
  SELECT b.id
  INTO id
  FROM b
  FORCE INDEX (ux_b_start)
  WHERE b.start_time <= event_date
  ORDER BY
    b.start_time DESC
  LIMIT 1;
  RETURN id;
END;

SELECT COUNT(*) FROM a;

1000


SELECT COUNT(*) FROM b;

200000

SELECT *
FROM (
  SELECT fn_get_last_b(a.event_time) AS bid,
         a.*
  FROM a
) ao, b FORCE INDEX (PRIMARY)
WHERE b.id = ao.bid
  AND b.end_time >= ao.event_time

1000 rows fetched in 0,0143s (0,1279s)

Answer 3

MySQL, MS SQL Server , 1, 1-2 .

, . , . , , start_time < end_time.

Answer 4

2 . > =, MySQL . , .

, MySQL , , , WHERE, .

Answer 5

( ), , .

, , , , , , , . , , :

SELECT *
FROM events
WHERE 
   ( start_time BETWEEN ( 'search_start' - INTERVAL 2 DAY ) and 'search_end' )
   AND end_time >= 'search_start'

... start_time.

(. 4 , 24 ... , , .)

Answer 6

. 1) 2) SQL, :

Table event_times
id
event_id
mark_time

, .

SELECT *
FROM events
LEFT JOIN event_times ON event_id = events.id
WHERE mark_time = '2009-02-18 16:27:12'

, " ", .. mark_time , .