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 3004 David Marcus 01/23/2013 06:35PM
Re: Select last row that is less than or equal to a date 804 Rick James 01/24/2013 10:27PM
Re: Select last row that is less than or equal to a date 786 David Marcus 01/26/2013 09:35AM
Re: Select last row that is less than or equal to a date 682 Rick James 01/27/2013 10:19AM
Re: Select last row that is less than or equal to a date 717 David Marcus 01/27/2013 12:00PM
Re: Select last row that is less than or equal to a date 611 Rick James 01/29/2013 12:51AM
Re: Select last row that is less than or equal to a date 646 David Marcus 01/29/2013 06:28PM
Re: Select last row that is less than or equal to a date 749 Øystein Grøvlen 01/30/2013 04:50AM
Re: Select last row that is less than or equal to a date 680 David Marcus 01/30/2013 05:50PM
Re: Select last row that is less than or equal to a date 623 Øystein Grøvlen 02/01/2013 06:03AM
Re: Select last row that is less than or equal to a date 656 David Marcus 02/02/2013 07:14AM
Re: Select last row that is less than or equal to a date 627 Øystein Grøvlen 02/02/2013 01:49PM
Re: Select last row that is less than or equal to a date 641 David Marcus 02/02/2013 02:25PM
Re: Select last row that is less than or equal to a date 609 Øystein Grøvlen 02/03/2013 05:58AM
Re: Select last row that is less than or equal to a date 636 David Marcus 02/03/2013 10:04AM
Re: Select last row that is less than or equal to a date 677 Rick James 02/03/2013 10:43AM
Re: Select last row that is less than or equal to a date 801 David Marcus 02/03/2013 12:33PM
Re: Select last row that is less than or equal to a date 676 Rick James 02/03/2013 02:59PM
Re: Select last row that is less than or equal to a date 706 David Marcus 02/03/2013 03:35PM
Re: Select last row that is less than or equal to a date 653 Rick James 02/03/2013 04:02PM
Re: Select last row that is less than or equal to a date 670 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.