Two methods of running this php query, which is more efficient ?

[jeddiki]

Hi,
I am comparing my update table to the latest data in in my main
data table, so I am selecting the latest data from the main table with this expression:

mday_no is an indexed column.

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

    $result_main = mysql_query($sql_main)
	or die("could not FIND ID in cb_main.". mysql_error());

However, I realized that I could also do this and I assume I would get the same result.

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

    $result_main = mysql_query($sql_main)
	or die("could not FIND ID in cb_main.". mysql_error());

Is one method more efficient that the other?

[dgreenhouse]

The second one is going to run faster whether mid is indexed or not - I assume it is.

In the second query, the query engine only has to do one index lookup whereas in the first, it has a sub-select and an aggregate function.

Run them both with EXPLAIN in a query analyzer:
i.e.

EXPLAIN SELECT * FROM cb_main WHERE mid = some_number AND mday_no=(select max(mday_no));

EXPLAIN SELECT * FROM cb_main WHERE mid = some_number ORDER BY  mday_no DESC limit 1

I just realized that the query engine "might just" optimize the first query better than the second in some cases. I'll have to think about that a bit, but as stated, it's best to run it through the query analyzer to determine what the query engine's execution plan will be.

[jeddiki]

The second one is going to run faster whether mid is indexed or not - I assume it is.

Yep, it is, mid and mday_no are indexed.


I don't have a query analyzer :(

Can you suggest where I can get one ?

[dgreenhouse]

You can use phpMyAdmin (if it's setup and probably would be on a commercial hosting site), the command prompt (assuming SSH [ or telnet - heaven forbid ] access), or a script in PHP. I'll show a little PHP script at the end.

Also, if you can make direct connections to the DB machine(s), you can install a number of tools on your local machine.

Two I use are:

MySQL Workbench (pro or community edition)

http://wb.mysql.com/

- And -

SQLyog (ultimate, enterprise, professional, or community)

http://webyog.com/en/index.php

- There are many more -


// El Cheap-o-php query analyzer...

// Connect…
mysql_connect('localhost','user','pwd');

// Add ‘EXPLAIN’ to head of your queries
$sql = 
  'EXPLAIN SELECT a.*
   FROM bytes_help.workorder a
   JOIN bytes_help.workorder b
   ON  b.id = mod(a.id,5)'; // A useless query...

// Do the query…
$rs = mysql_query($sql);

// Dump the execution plan...
print '<pre>'; // encapsulate with pre's to keep from having to 'view source'
while ($row = mysql_fetch_assoc($rs)) {
  // Fetch into an associative array...
  print_r($row);
}
print '</pre>';

// Output...
Array
(
    [id] => 1
    [select_type] => SIMPLE
    [table] => a
    [type] => ALL
    [possible_keys] => 
    [key] => 
    [key_len] => 
    [ref] => 
    [rows] => 101
    [Extra] => 
)
Array
(
    [id] => 1
    [select_type] => SIMPLE
    [table] => b
    [type] => eq_ref
    [possible_keys] => PRIMARY
    [key] => PRIMARY
    [key_len] => 4
    [ref] => func
    [rows] => 1
    [Extra] => Using where; Using index
)

[Markus]

HeidiSQL is a good one :)