Mysql – Convert date in date list condition to list of date ranges

conditiondateMySQLperformancequery-performance

I want to look for all the records that occur on specific dates.

SELECT *
FROM table1
WHERE date(column) in ($date1, $date2, ...);

However, as many of you, know this kind of comparison doesn't get along with indexes. So, I was wondering if there is a simple way to convert this query to something of the style of the following query without too much effort (i.e., : not using an external tool).

SELECT *
FROM table1
WHERE (column >= $date1 AND column < $date1 + interval 1 day)
   OR (column >= $date2 AND column < $date2 + interval 1 day)
   ...

So the optimizer can still use the indexes. (I'm using MySQL, but ANSI SQL would be great)

Best Answer

SUGGESTION #1

SELECT A.* FROM table1 A INNER JOIN
(
    SELECT '2015-03-01' dtcolumn
    UNION SELECT '2015-03-15'
    UNION SELECT '2015-04-01'
    UNION SELECT '2015-04-15'
    UNION SELECT '2015-05-01'
) B ON
A.dtcolumn >= B.dtcolumn
AND A.dtcolumn < B.dtcolumn + INTERVAL 1 DAY;

SUGGESTION #2

SELECT * FROM table1 WHERE
(column >= '2015-03-01' AND
column < '2015-03-01' + INTERVAL 1 DAY)
UNION
SELECT * FROM table1 WHERE
(column >= '2015-03-15' AND
column < '2015-03-15' + INTERVAL 1 DAY)
UNION
SELECT * FROM table1 WHERE
(column >= '2015-04-01' AND
column < '2015-04-01' + INTERVAL 1 DAY)
UNION
SELECT * FROM table1 WHERE
(column >= '2015-04-15' AND
column < '2015-04-15' + INTERVAL 1 DAY)
UNION
SELECT * FROM table1 WHERE
(column >= '2015-05-01' AND
column < '2015-05-01' + INTERVAL 1 DAY);

Related Solutions

Sql-server – How to speed up query on table with millions of rows

On reason this can happen is that you're using local variables.

The problem is that this query takes so much time to go, despite all of the indexes i've made on different columns.

Here's an example using a similar setup. In the Stack Overflow schema there's a narrow-ish table called Votes that looks like this.

With no index on CreationDate, our only option would be to scan the Clustered Index. But if we create one only on CreationDate, the optimizer can choose to use that index if it thinks doing a Key Lookup for the rest of the columns is cheaper than scanning the Clustered Index and applying a predicate.

CREATE INDEX ix_yourmom ON dbo.Votes(CreationDate)

For this query:

DECLARE @StartDate DATETIME = '2010-07-01';
DECLARE @EndDate DATETIME = '2010-07-02';

SELECT *
FROM   dbo.Votes AS v
WHERE  v.CreationDate BETWEEN @StartDate AND @EndDate;
GO

The cardinality estimate for unknown variables using between is 16.4317%. That leads to a clustered index scan and a missing index request for an index that covers the entire query.

If you run the query with RECOMPILE, you allow for the parameter embedding optimization.

DECLARE @StartDate DATETIME = '2010-07-01';
DECLARE @EndDate DATETIME = '2010-07-02';

SELECT *
FROM   dbo.Votes AS v
WHERE  v.CreationDate BETWEEN @StartDate AND @EndDate
OPTION ( RECOMPILE );

Which gives us a different query plan, and a more accurate estimate.

Hope this helps!

Mysql – Indexes involved in intervals collision detection

This is exactly the kind of query that can benefit from a composite, or multiple-column index: when a single statement filters by multiple columns in the same table. The order of columns in the index will depend on your use cases, but in general the order in which the columns appear in the DML statement is fine, in your case (assuming MySQL but the concept is the same for most RDBMSs):

ALTER TABLE 'venue'
ADD INDEX ('startdate','enddate')

See the documentation for other alternatives, such as adding a column containing the hash of multiple columns.