1

MySQL.

Let there is a table with fields startdate and kind.

Actually in the real problem column named here startdate and kind are in a UNIQUE index, but the case if only startdate (not kind) is UNIQUE would be also interesting.

Let a date d is specified. I want to retrieve the data of given kind which corresponds to the latest startdate which is before d.

As far as I understand this cannot be efficiently done with views.

So, should it be done with a stored procedure? I am a novice in the topic of stored procedures. Your code would be welcome.

2 Answers 2

1

This is easy.

let @d be the date and @k be the kind :

select *
from table a
where 
  startdate = (select max(b.startdate) 
               from table b 
               where b.startdate < @d
               and b.kind = @k)
  and a.kind = @k
3
  • Your code does not restrict query only to fields with a specified kind. Please rewrite the query. I suppose that rewriting it in the most trivial way would make it inefficient. Probably we should use LIMIT 1 instead?
    – porton
    Apr 23, 2013 at 16:54
  • I edited my question to make clear that we want only data of a given kind.
    – porton
    Apr 23, 2013 at 16:55
  • I've updated the query
    – druzin
    Apr 23, 2013 at 17:15
1

I would like to suggest the following:

SUGGESTION #1 : Query as follows

SET @GivenKind = '...';
SET @GivenDate = d;
SELECT * FROM table WHERE kind=@GivenKind
AND startdate < @GivenDate
ORDER BY startdate DESC LIMIT 1;

SUGGESTION #2 : Define a Good Index

In your question you said

Actually in the real problem column named here startdate and kind are in a UNIQUE index, but the case if only startdate (not kind) is UNIQUE would be also interesting.

For my suggested query, you will need an index to support the WHERE and ORDER BY

ALTER TABLE table ADD INDEX kind_startdate_ndx (kind,startdate);

This will help the Query Optimizer immensely. Why?

  • kind is a constant in the query
  • startdate is in a range within the kind
  • Order of the columns in the index matters

If this index does not exist, it could result in a range index/table scan or full index/table scan (if the kind's cardinality is too low).

Having an index with startdate,kind will result in a range index scan. If you specify the kind.

Give it a Try !!!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.