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 3139 David Marcus 01/23/2013 06:35PM
Re: Select last row that is less than or equal to a date 845 Rick James 01/24/2013 10:27PM
Re: Select last row that is less than or equal to a date 834 David Marcus 01/26/2013 09:35AM
Re: Select last row that is less than or equal to a date 736 Rick James 01/27/2013 10:19AM
Re: Select last row that is less than or equal to a date 754 David Marcus 01/27/2013 12:00PM
Re: Select last row that is less than or equal to a date 647 Rick James 01/29/2013 12:51AM
Re: Select last row that is less than or equal to a date 697 David Marcus 01/29/2013 06:28PM
Re: Select last row that is less than or equal to a date 827 Øystein Grøvlen 01/30/2013 04:50AM
Re: Select last row that is less than or equal to a date 729 David Marcus 01/30/2013 05:50PM
Re: Select last row that is less than or equal to a date 680 Øystein Grøvlen 02/01/2013 06:03AM
Re: Select last row that is less than or equal to a date 697 David Marcus 02/02/2013 07:14AM
Re: Select last row that is less than or equal to a date 678 Øystein Grøvlen 02/02/2013 01:49PM
Re: Select last row that is less than or equal to a date 682 David Marcus 02/02/2013 02:25PM
Re: Select last row that is less than or equal to a date 646 Øystein Grøvlen 02/03/2013 05:58AM
Re: Select last row that is less than or equal to a date 672 David Marcus 02/03/2013 10:04AM
Re: Select last row that is less than or equal to a date 729 Rick James 02/03/2013 10:43AM
Re: Select last row that is less than or equal to a date 848 David Marcus 02/03/2013 12:33PM
Re: Select last row that is less than or equal to a date 716 Rick James 02/03/2013 02:59PM
Re: Select last row that is less than or equal to a date 741 David Marcus 02/03/2013 03:35PM
Re: Select last row that is less than or equal to a date 695 Rick James 02/03/2013 04:02PM
Re: Select last row that is less than or equal to a date 731 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.