MySQL: Most efficient method retrieving single result.

[Belmin]

Hi all,

Wanted to know what is the most efficient way of doing a select query
for mysql that only returns one value. For example:

$mysqli->query('select count(*) from log');
$temprec = $result->fetch_assoc();
$count = $temprec['count'];

That doesn't seem efficient. How should I do it? Or is this as efficient
as it gets?

--

Belmin Fernandez

Visit: http://www.belminfernandez.com/homepage
Email: belminf at gmail period com

[Belmin]

Oh sorry, using PHP.

Written by "Belmin" on 2/21/05 7:05p:

Hi all,

Wanted to know what is the most efficient way of doing a select query
for mysql that only returns one value. For example:

$mysqli->query('select count(*) from log');
$temprec = $result->fetch_assoc();
$count = $temprec['count'];

That doesn't seem efficient. How should I do it? Or is this as efficient
as it gets?

--

Belmin Fernandez

Visit: http://www.belminfernandez.com/homepage
Email: belminf at gmail period com

[Bill Turczyn]

http://dev.mysql.com/doc/mysql/en/sh...le-status.html
To get information about a table ie. rows, size, etc. you could use:
$mysqli->query("show table status like 'log'");
$temprec = $result->fetch_assoc();
print_r ($temprec);
Also you could use the LIMIT clause at the end of you query:

$mysqli->query("select * from log limit 1");
$temprec = $result->fetch_assoc();
print_r ($temprec);

http://dev.mysql.com/doc/mysql/en/li...imization.html
http://dev.mysql.com/doc/mysql/en/select.html

The LIMIT clause can be used to constrain the number of rows returned
by the SELECT statement. LIMIT takes one or two numeric arguments,
which must be integer constants.

With two arguments, the first argument specifies the offset of the
first row to return, and the second specifies the maximum number of
rows to return. The offset of the initial row is 0 (not 1):

mysql> SELECT * FROM table LIMIT 5,10; # Retrieve rows 6-15