MySQL Forums
Forum List  »  Optimizer & Parser

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
6837
January 23, 2013 06:35PM


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.