Skip navigation links

MySQL Forums :: Optimizer & Parser :: Select last row that is less than or equal to a date


Advanced Search

Select last row that is less than or equal to a date
Posted by: David Marcus ()
Date: January 23, 2013 06:35PM

I have a table with an index on a date. I want to get the last row
whose date is less than or equal to a certain date. I would hope that
MySQL could do this and only read one row. However, looking at the
output of the "explain", I'm not sure what MySQL is doing. Is MySQL
reading more than one row, and if so, can the query be sped up? This
is with MySQL 5.5.29. Here is the table:

create table History (
HistoryEvent integer unsigned not null, # EventID
HistoryPlayer integer unsigned not null, # PlayerID
HistoryDate date not null,
HistoryReportID integer not null,
HistoryInitialMean smallint unsigned not null,
HistoryInitialStDev smallint unsigned not null,
HistoryFinalMean smallint unsigned not null,
HistoryFinalStDev smallint unsigned not null,
primary key ( HistoryEvent, HistoryPlayer ),
unique index EventReportID ( HistoryEvent, HistoryReportID ),
index EventInitialMean ( HistoryEvent, HistoryInitialMean ),
index EventFinalMean ( HistoryEvent, HistoryFinalMean ),
index PlayerDate ( HistoryPlayer, HistoryDate ) )
default character set latin1 collate latin1_german1_ci pack_keys=1 engine=MyISAM;

Here is the query and the output from "explain":

explain extended select * from History where HistoryPlayer = 59161 and
HistoryDate <= '2012-01-01' order by HistoryDate desc limit 0,1

"id","select_type","table","type","possible_keys","key","key_len","ref","rows","filtered","Extra"
1,"SIMPLE","History","range","PlayerDate","PlayerDate","7","",67,100.00,"Using where"

This one retrieves many rows, but the output from "explain" is the
same:

explain extended select * from History where HistoryPlayer = 59161 and
HistoryDate <= '2012-01-01' order by HistoryDate desc

"id","select_type","table","type","possible_keys","key","key_len","ref","rows","filtered","Extra"
1,"SIMPLE","History","range","PlayerDate","PlayerDate","7","",67,100.00,"Using where"

This one checks for a specific date, and "explain" says it will only
read one row:

explain extended select * from History where HistoryPlayer = 59161 and
HistoryDate = '2012-01-01' order by HistoryDate desc limit 0,1

"id","select_type","table","type","possible_keys","key","key_len","ref","rows","filtered","Extra"
1,"SIMPLE","History","ref","PlayerDate","PlayerDate","7","const,const",1,100.00,""

Here are the first two queries with a different value of
HistoryPlayer:

explain extended select * from History where HistoryPlayer = 59185 and
HistoryDate <= '2012-01-01' order by HistoryDate desc limit 0,1

"id","select_type","table","type","possible_keys","key","key_len","ref","rows","filtered","Extra"
1,"SIMPLE","History","range","PlayerDate","PlayerDate","7","",29,100.00,"Using where"

explain extended select * from History where HistoryPlayer = 59185 and
HistoryDate <= '2012-01-01' order by HistoryDate desc

"id","select_type","table","type","possible_keys","key","key_len","ref","rows","filtered","Extra"
1,"SIMPLE","History","range","PlayerDate","PlayerDate","7","",29,100.00,"Using where"

Options: ReplyQuote


Subject Views Written By Posted
Select last row that is less than or equal to a date 2733 David Marcus 01/23/2013 06:35PM
Re: Select last row that is less than or equal to a date 740 Rick James 01/24/2013 10:27PM
Re: Select last row that is less than or equal to a date 715 David Marcus 01/26/2013 09:35AM
Re: Select last row that is less than or equal to a date 623 Rick James 01/27/2013 10:19AM
Re: Select last row that is less than or equal to a date 667 David Marcus 01/27/2013 12:00PM
Re: Select last row that is less than or equal to a date 552 Rick James 01/29/2013 12:51AM
Re: Select last row that is less than or equal to a date 578 David Marcus 01/29/2013 06:28PM
Re: Select last row that is less than or equal to a date 668 Øystein Grøvlen 01/30/2013 04:50AM
Re: Select last row that is less than or equal to a date 629 David Marcus 01/30/2013 05:50PM
Re: Select last row that is less than or equal to a date 561 Øystein Grøvlen 02/01/2013 06:03AM
Re: Select last row that is less than or equal to a date 614 David Marcus 02/02/2013 07:14AM
Re: Select last row that is less than or equal to a date 570 Øystein Grøvlen 02/02/2013 01:49PM
Re: Select last row that is less than or equal to a date 588 David Marcus 02/02/2013 02:25PM
Re: Select last row that is less than or equal to a date 551 Øystein Grøvlen 02/03/2013 05:58AM
Re: Select last row that is less than or equal to a date 585 David Marcus 02/03/2013 10:04AM
Re: Select last row that is less than or equal to a date 621 Rick James 02/03/2013 10:43AM
Re: Select last row that is less than or equal to a date 747 David Marcus 02/03/2013 12:33PM
Re: Select last row that is less than or equal to a date 614 Rick James 02/03/2013 02:59PM
Re: Select last row that is less than or equal to a date 662 David Marcus 02/03/2013 03:35PM
Re: Select last row that is less than or equal to a date 591 Rick James 02/03/2013 04:02PM
Re: Select last row that is less than or equal to a date 621 David Marcus 02/03/2013 04:15PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.