MySQL按指定时间范围高效查询DATETIME类型数据的方法

Hey there! Let’s fix that inefficient LIKE query you’re using for your time-range data pulls. With 100 rows inserted every minute, you need a solution that scales—and LIKE just won’t cut it once your table gets big (it bypasses indexes entirely, leading to slow full-table scans).

First, Fix the Indexing #

First things first: make sure your time column has a regular B-tree index set up. This is the foundation for fast range queries. If you haven’t done this yet, run:

CREATE INDEX idx_time ON your_table(time);

Replace LIKE with Date-Time Range Comparisons #

Instead of converting your time column to a string with LIKE, use native DATETIME range operators (>= and <). These play nicely with indexes and are way faster.

Example 1: 15-Minute Time Range #

Let’s say you want data from 2024-05-20 14:00:00 to 2024-05-20 14:15:00. Use this query:

SELECT * FROM your_table
WHERE time >= '2024-05-20 14:00:00'
  AND time < '2024-05-20 14:15:00';

Note: Using < instead of <= avoids edge cases where a record has exactly 14:15:00 (which shouldn’t be included in the 00-15 window).

If your time column only stores up to the hour (e.g., all values are YYYY-MM-DD HH:00:00), then a 15-minute window within the same hour will just match all records for that hour. In that case, a simple equality check works even better:

SELECT * FROM your_table WHERE time = '2024-05-20 14:00:00';

Example 2: 1-Hour Time Range #

For a full hour window (e.g., 14:00 to 15:00), use:

SELECT * FROM your_table
WHERE time >= '2024-05-20 14:00:00'
  AND time < '2024-05-20 15:00:00';

Pro Tip: Precompute Time-Granularity Columns (For Repeated Queries) #

If you’re frequently querying by 15-minute, 1-hour, or other fixed intervals, add a computed column to your table to store the interval identifier. This lets you use ultra-fast exact matches instead of range checks.

Step 1: Add the Column #

For 15-minute intervals, add a column like time_15min (we’ll use a string for readability, but an integer works too):

ALTER TABLE your_table ADD COLUMN time_15min VARCHAR(12);

Step 2: Populate It on Insert #

When inserting new rows, calculate the 15-minute bucket for the current time. For example:

INSERT INTO your_table (time, time_15min, [other_columns])
VALUES (
  DATE_FORMAT(NOW(), '%Y-%m-%d %H:00:00'), -- Your existing time value
  DATE_FORMAT(NOW(), '%Y%m%d%H') + FLOOR(MINUTE(NOW())/15)*15,
  -- Insert other values here
);

This will generate values like 202405201400 for 14:00-14:15, 202405201415 for 14:15-14:30, etc.

Step 3: Index the New Column #

CREATE INDEX idx_time_15min ON your_table(time_15min);

Step 4: Query with Exact Matches #

Now you can pull 15-minute data in a flash:

SELECT * FROM your_table WHERE time_15min = '202405201400';

Verify Your Indexes Are Working #

Always double-check that MySQL is using your indexes with the EXPLAIN command. Run this before and after your changes to confirm:

EXPLAIN SELECT * FROM your_table WHERE time >= '2024-05-20 14:00:00' AND time < '2024-05-20 14:15:00';

Look for range or ref in the type column—this means the index is being used. If you see ALL, that’s a full-table scan, so double-check your index setup.

内容的提问来源于stack exchange，提问作者Hagay