在mysql查询中使用LIMIT会使我的应用程序更快更有效吗？

Question

Assuming i have more than 1 million records in a table. Which of the codes below will return results faster and efficient? I used php.

Should I use this?

$query = mysql_query("select count(*) as count from reversals where seen = 'false'");
while($q = mysql_fetch_array($query))
    {
    $limit = $q['count'];
    }

$query2 = mysql_query("select * from reversals where seen = 'false' limit $limit");
while($q = mysql_fetch_array($query))
    {
    echo $q['amount'];
    }

OR:

$query = mysql_query("select * from reversal where seen = 'false'");
while($q = mysql_fetch_array($query2))
    {
    echo $q['amount'];
    }

Answer 1

Ok to answer your question here is it, I have a table called paymentlog and it has 709231 records in it. For the test purpose I made sure that it does not have any index and specially the column used in where condition.

I used EXPLAIN and I got something as

explain select * from paymentlog where transdate = '2012-12-01' limit 10 ; 

+----+-------------+------------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+------------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | paymentlog | ALL  | NULL          | NULL | NULL    | NULL | 709231 | Using where |
+----+-------------+------------+------+---------------+------+---------+------+--------+-------------+

You can see its scanning all the rows in the table even through I have added the LIMIT. So LIMIT does not make your query faster it only reduced the data volume.

Now If I add an index in the above table for transdate and run the same explain , I get

+----+-------------+------------+------+--------------------+--------------------+---------+-------+------+-------+
| id | select_type | table      | type | possible_keys      | key                | key_len | ref   | rows | Extra |
+----+-------------+------------+------+--------------------+--------------------+---------+-------+------+-------+
|  1 | SIMPLE      | paymentlog | ref  | plog_transdate_idx | plog_transdate_idx | 3       | const | 1069 |       |
+----+-------------+------------+------+--------------------+--------------------+---------+-------+------+-------+

So the scanned rows now reported is 1069, even though it is not likely that it will scan 1069 rows it will stop when the limit value is found but if we are getting the values with limit say 1000,70 then it will need to do scan till 1069,so it reduced the scanning rows in the table due to index on the column in where condition, better than 709231.

So the conclusion is by using index you can reduced the number rows being scanned, but if you limit the same number of rows will be scanned with at-most index 1069 and without index 709231.