MySQL Select efficient first and last row

Question

I want to get two rows from my table in a MySQL databse. these two rows must be the first one and last one after I ordered them. To achieve this i made two querys, these two:

SELECT dateBegin, dateTimeBegin FROM worktime ORDER BY dateTimeBegin ASC LIMIT 1;";
SELECT dateBegin, dateTimeBegin FROM worktime ORDER BY dateTimeBegin DESC LIMIT 1;";

I decided to not get the entire set and pick the first and last in PHP to avoid possibly very large arrays. My problem is, that I have two querys and I do not really know how efficient this is. I wanted to combine them for example with UNION, but then I would still have to order an unsorted list twice which I also want to avoid, because the second sorting does exactly the same as the first

I would like to order once and then select the first and last value of this ordered list, but I do not know a more efficient way then the one with two querys. I know the perfomance benefit will not be gigantic, but nevertheless I know that the lists are growing and as they get bigger and bigger and I execute this part for some tables I need the most efficient way to do this. I found a couple of similar topics, but none of them adressed this particular perfomance question.

Any help is highly appreciated.

why not something like `select datebegin, max(datetimebegin), min(datetimebegin) ...`? — Marc B, Jan 04 '16 at 20:37
I read something like that but could not figure out how the work exactly. Why I dod not choose these was that I am not sure whether they sort twice or not? — JRsz, Jan 04 '16 at 20:39
there'd be no sorting at all. you'd get the largest/smallest datetimebegins value, and the first-encounted datebegin. — Marc B, Jan 04 '16 at 20:40
Ok, now I feel stupid. Thank you a lot. I was so fixed in the sorting problem that I misinterpreted this as one as well. If you want you can put your first comment as an answer and I will accept it. Thank you a lot, sometimes there is just another mind needed :) — JRsz, Jan 04 '16 at 20:41
@MarcB Wouldn't work if dateBegin is actually significant, that is needs to be from the first and last row in datetimebegin order. — Joachim Isaksson, Jan 04 '16 at 20:42
http://stackoverflow.com/questions/1379565/mysql-first-and-last-record-of-a-grouped-record-aggregate-functions — ob_start, Jan 04 '16 at 20:43
@JoachimIsaksson from the name of the column `dateBegin` it seems, that in fact it could even be calculated from `dateTimeBegin`. — Kamil Gosciminski, Jan 04 '16 at 20:49
The dateBegin field is not important since it has less information than dateTimeBegin, it is simply to avoid doing the same transformation every time a specific page is loaded. I do not entirely understand what you mean @JoachimIsaksson — JRsz, Jan 04 '16 at 20:55
@JRsz If the value of dateBegin is unimportant, there is no problem. — Joachim Isaksson, Jan 04 '16 at 20:58

score 2 · Answer 1 · answered Jan 04 '16 at 20:42

Your queries are fine. What you want is an index on worktime(dateTimeBegin). MySQL should be smart enough to use this index for both the ASC and DESC sorts. If you test it out, and it is not, then you'll want two indexes: worktime(dateTimeBegin asc) and worktime(dateTimeBegin desc).

Whether you run one query or two is up to you. One query (connected by UNION ALL) is slightly more efficient, because you have only one round-trip to the database. However, two might fit more easily into your code, and the difference in performance is unimportant for most purposes.

score 2 · Accepted Answer · answered Jan 05 '16 at 00:12

(This is both an "answer" and a rebuttal to errors in some of the comments.)

INDEX(dateTimeBegin)

will facilitate SELECT ... ORDER BY dateTimeBegin ASC LIMIT 1 and the corresponding row from the other end, using DESC.

MAX(dateTimeBegin) will find only the max value for that column; it will not directly find the rest of the columns in that row. That would require a subquery or JOIN.

INDEX(... DESC) -- The DESC is ignored by MySQL. This is almost never a drawback, since the optimizer is willing to go either direction through an index. The case where it does matter is ORDER BY x ASC, y DESC cannot use INDEX(x, y), nor INDEX(x ASC, y DESC). This is a MySQL deficiency. (Other than that, I agree with Gordon's 'answer'.)

( SELECT ... ASC )
UNION ALL
( SELECT ... DESC )

won't provide much, if any, performance advantage over two separate selects. Pick the technique that keeps your code simpler.

You are almost always better off having a single DATETIME (or TIMESTAMP) field than splitting out the DATE and/or TIME. SELECT DATE(dateTimeBegin), dateTimeBegin ... works simply, and "fast enough". See also the function DATE_FORMAT(). I recommend dropping the dateBegin column and adjusting the code accordingly. Note that shrinking the table may actually speed up the processing more than the cost of DATE(). (The diff will be infinitesimal.)

Without an index starting with dateTimeBegin, any of the techniques would be slow, and get slower as the table grows in size. (I'm pretty sure it can find both the MIN() and MAX() in only one full pass, and do it without sorting. The pair of ORDER BYs would take two full passes, plus two sorts; 5.6 may have an optimization that almost eliminates the sorts.)

If there are two rows with exactly the same min dateTimeBegin, which one you get will be unpredictable.

Thank you for your answer. The last point you mentioned is fine, because I am only interested in the lowest value and therefor they may appear as often as they want. Since the value of dateTimeBegin is the only interesting value for me (I actually do not use the other anymore, so this caused some confusion and will be deleted from my question) this query works for me: `SELECT MIN(dateTimeBegin) AS min, MAX(dateTimeBegin) AS max FROM worktime;` — JRsz, Jan 05 '16 at 08:47

MySQL Select efficient first and last row

2 Answers2