mysql查询速度的差异

Question

Suppose i have 1kk records in my database.

Now i need to select some data, and also i need to know how many fields did i select, so my question is:

Is it better to run one query to count data like this:

SELECT COUNT("id") from table where something = 'something'

And after that run one more querio for selection like this:

SELECT 'some_field' from table where something = 'something';

Or Maybe it's better to just select data and then just count it with php like:

count($rows);

Or maybe there is even better ways to do it, for example do it all in one query?

Answer 1

Reading between the lines, I think what your are probably after is SQL_CALC_FOUND_ROWS. This allows you to select part of a result set (using a LIMIT clause), and still calculate the total number of matching rows in a single operation. You still use two queries, but the actual search operation in the data only happens once:

// First get the results you want...
$result = mysql_query("
  SELECT SQL_CALC_FOUND_ROWS
  FROM `table`
  WHERE `something` = 'something'
  LIMIT 0, 10
");

// ...now get the total number of results
$numRows = mysql_query("
  SELECT FOUND_ROWS()
");
$numRows = mysql_fetch_row($numRows);
$numRows = $numRows[0];