Whicj of these two php query methods is more efficient ?

habbardone

When selecting a product acording to the value of
one of its fields it looks like I could use max(columnX)

or I could use Order By columnX DESC limit 1

Looks like they would do the same thing.

I have set up an index based on columnX

In my example I have:

$sql_main = "SELECT * FROM cb_main WHERE mid = '$the_id' AND mday_no=(select max(mday_no)";

OR I could use:

$sql_main = "SELECT * FROM cb_main WHERE mid = '$the_id'  ORDER BY  mday_no DESC limit 1";

Is one of the methods more efficient than the other ?

Thanks for any insight, experience.

Weedpacket

Well, for one thing, that's not PHP, it's SQL.

It depends on whether mday_no is indexed and what stats the DBMS has accumulated on the contents of the table, and also on the details of how your DBMS compiles queries into execution plans; but at a first approximation it would look to me that the first query would run at worst in O(n) time (most of it spent looking for the largest value) while the second would need O(n log n) for n records (because it sorts the whole table before looking to see which record floated to the top in the process).

Moving this to the database forum.

habbardone

It depends on whether mday_no is indexed

I have set up an index based on columnX

May I should have explained that in my example columnX is
of course mday_no .

I thought the idea of having an index was to avoid the
need to

sort the whole table before looking to see which record floated to the top in the process

I would think that the index would benefit both
methods of operation so I was hoping someone my know
from experience which was faster.

Is there some kind of query timer somewhere
or would it be easy to write one to find out the answer ?

Thanks

johanafm

But the first query isn't correct. It looks as if half has been cut away. Consider

id   mday_no
1   2
1   20
2  28

Which would turn

"SELECT * FROM cb_main WHERE mid = '$the_id' AND mday_no=(select max(mday_no)";

into

"SELECT * FROM cb_main WHERE mid = '$the_id' AND mday_no=28";

=> no results found

You lack the mid comparison in the nested query.

$sql_main = "SELECT * FROM cb_main WHERE mid = '$the_id' AND mday_no=(select max(mday_no) FROM cb_main WHERE mid = '$the_id')";

Which make me belive the use of subquery runs in O(m n), where n is the number of records in the table (for the subquery) and m is the number of records with mid = $the_id.
And I doubt the ORDER BY query taking O(n log n). It should not need to sort the whole table first. And since the limit 1 is there, the sorting on the set of size m should stop once the first 1 records have been "sorted".

But it never hurts to see if belief is fact.

$sql1 = '...';
$sql 2 = '...';

$start1 = microtime(true);
mysql_query($sql1);
$end1 = microtime(true);

$start2 = microtime(true);
mysql_query($sql2);
$end2 = microtime(true);

$diff1 = $end1 - $start1;
$diff2 = $end2 - $start2;

habbardone

Thanks for your insightful post Johanafm.

You are correct about my query, I was wanting to obtain the max(mday_no)
for the subset of mid = '$the_id'.
So my query was flawed.

The code you put is for the testing of
the two methods I guess.

I would need to supply the table - how many records ?

The one I have is about 10,000 and grows by 10,000 everyday,
which is why I want to select the most efficient method.

habbardone

AND THE RESULTS ARE:

$sql1 = "SELECT * FROM cb_main WHERE mid = '$the_id' AND mday_no=(select max(mday_no) FROM cb_main WHERE mid = '$the_id')"; 

$sql2 = "SELECT * FROM cb_main WHERE mid = '$the_id'  ORDER BY  mday_no DESC limit 1";

Record near front of table

Speed of query 1: 0.0015139579772949
Speed of query 2: 0.0010380744934082

Record near middle of table:

Speed of query 1: 0.0015428066253662
Speed of query 2: 0.0011389255523682

Record near end of table:

Speed of query 1: 0.0016839504241943
Speed of query 2: 0.0011599063873291

So query 2 wins out every time.

Thanks all for your help 🙂

johanafm

Indexing will be far more important, even if it's always good to optimize as far as you can.

Testing the two versions on a table with 4.5 million rows, and 640 matching the id, the subquery takes roughly 6 times longer than the order by. 6ms vs 0.9ms. Yours seem to be be closer in execution time. Which may depend on dbms, indexes, db settings...

But to get any definitive results, you'd need to set up test data to see how they change for varying numbers of m and n, although I'd say it's only m that matters in this case, where m is the number of rows matching the id, and n is the total in the table.
What happens if m is 100k? If there is still a difference of 5ms or is one still 6 times slower than the other?
It's easy enough to create a script that fills up a test table with data to test things like this.

habbardone

Thanks for your feed back.

On my table of 10,000 rows only one row
would match - but after a year the table would
be 3,650,000 and have approx 200 matches.

Both mid and mday_no are indexed.

I am glad that your results also favored the second query
(with out the sub query).