I want to get first and last row in table, and i found 2 way to do it:
$first = DB::table('shops')->first();
$last = DB::table('shops')->orderBy('id','DESC')->first();
And:
$shops = DB::table('shops')->get();
$first2 = $shops[0];
$last2 = $shops[count($shops)-1];
My question is, which way perform faster with the same DB? or any way log query time?
DB maybe large, 1.000 rows, 10.000 rows, etc,...
For about 8000 rows, here are the results:
The first way:
[2016-03-01 19:14:11] local.DEBUG: select * from `shops` limit 1; in 1.27 ms
[2016-03-01 19:14:11] local.DEBUG: select * from `shops` order by `id` desc limit 1; in 3.04 ms
The second way:
local.DEBUG: select * from `shops`; in 188.98 ms
You can see that the second way totally slower than the first.
Because in the second way you have to get all records from shops table. It takes a lot of times.
For bigger data set, I think the second way will not work because of timeout in request.
Update:
Just for another experiment.
I try the third way to resolve your problem in one query like the following:
$shops = DB::table('shops')
->whereRaw('id = (SELECT MIN(id) from shops)')
->orWhereRaw('id = (Select MAX(id) from shops)')
->get();
And I compare with the first way. And here is the result:
# the 3rd way
[2016-03-01 19:51:56] local.DEBUG: select * from `shops` where id = (SELECT MIN(id) from shops) or id = (Select MAX(id) from shops); in 1.04 ms
# the 1st way
[2016-03-01 19:52:02] local.DEBUG: select * from `shops` limit 1; in 0.67 ms
[2016-03-01 19:52:02] local.DEBUG: select * from `shops` order by `id` desc limit 1; in 0.5 ms
It seems that with a subquery the query time is faster.
184.67ms more. Which is huge for two queries trying to get the same result.
The difference between the timing on the queries will really come down to how quickly your machine can process them, and of course the number of rows it is working with.
I can tell you now, though, that the first solution is only grabbing one result in each query and so will be much quicker. You are fetching only two rows with two queries.
The second is loading all of your rows in to an array and then you are grabbing the first and last, with 1,000 to 10,000 rows this could take a fair bit of time.
You could always try run the queries manually and see just how long they take to load to get an actual difference.
You can get the exact SQL executed by adding toSql() to the end of the eloquent query, like so.
Like I commented: The first example will be way faster. Because you're only getting two records. The last example will get all the records, which in your example might be thousands of records. That's a huge difference in page loading time.
If you want to see how long your page loading is you might want to check barryvdh/laravel-debugbar. Which will give great information about all sorts of things, including page loading time.
orderByalso sort all rows.