I’m just wondering if there’s an advantage to using one method over another.
In a database with 22 records, I don’t think I have to much worry about performance. But someday I might have to do something similar at work, so I wanted to know the best way to do it.
Not using LIMIT would make the command more portable if you are thinking of switching to a different SQL database.
When it comes to the efficiency of different calls to receive the same info, it often depends on just how many records there are and what they contain as to which will be more efficient. Until such time as the two EXPLAIN statements give significantly different results there is no particular reason to switch.
Why would it? From what I can see both the alternatives being discussed produce one row of output - one using LIMIT and one using an alternative way of getting just the one row. That alternative would also work in databases that do not support the proprietary LIMIT command.
Please explain how SELECT ride_name, dist FROM ridelog WHERE dist = ( SELECT MAX(dist) FROM ridelog ) generate way too many rows.
I don’t understand this comment. We were discussing two specific alternative ways of coding an SQL call and I pointed out that using LIMIT makes the code less portable because it ties the code specifically to mySQL. I was simply pointing out that additional difference between the two versions beyond that of the comparative efficiency.
What does hair splitting have to do with that? Unless it has something to do with your misinterpretation of statements by not reading them in the context of what comes before. Does everyone have to quote everything that came before when making a comment in order for you to interpret the context of their comment properly?
Just for you - here’s the context of my earlier statement:
I know I’m late and I don’t pretend to be an expert but my understanding would be that the first would be faster as using subqueries tends to be slower.
Haven’t said this, each database is a world on its own and the best thing would be to test each of the queries on both databases to know what works best because, yes, ordering is slow
My problem is that I compare database functions to how I’d do the task myself.
If I had a stack of cards numbered 1 - 50, putting them in numeric order would take a certain amount of time. Picking the highest one would also require me to put them in order, then pick the top one (which is what I’d do with LIMIT 1) so from a human standpoint, they seem the same.
I don’t know how a database handles things though. It’s obviously faster at math than I am!
suppose you have a table where some column has various values… like, say, a table of rides where each ride has a PK and also a column for the distance
how do you image MAX(dist) is found? wouldn’t the engine have to go through all the rows somehow? i can see it examining all the rows and keeping a variable which it replaces every time it finds a larger value
now imagine you had to return only one row from a result set which is sorted… wouldn’t it be easier, instead of sorting them all, simply to scan through and find the largest value, then return that row?
what i’m saying is, we can speculate all we want about what’s fast, and what’s faster, but in the end, the optimizer decides how to do what you ask it to do, and it might not actually sort if it doesn’t have to
if you want to know what’s fast and what’s faster, there is only one way – EXPLAIN