MySQL query performance

The following 2 queries are used for the same purpose to get data in a range, but the time cost is quite different.

$query_1 = "SELECT import_id, amount FROM import_tbl WHERE ABS(amount-99.99)<0.001 AND import_id>=$start AND import_id<=$end ORDER BY import_id";
Empty set (2.05 sec)

$query_2 = "select import_id, amount from import_tbl where ABS(amount-99.99)<0.001 limit $cnt, $offset";
Empty set (27.71 sec)

When using EXPLAIN in mysql, you can see that query_2 scans all the rows in the table, while query_1 just search the rows fall in the range.

EXPLAIN Syntax

EXPLAIN tbl_name

Or:

EXPLAIN [EXTENDED] SELECT select_options

The EXPLAIN statement can be used either as a synonym for DESCRIBE or as a way to obtain information about how MySQL executes a SELECT statement:

* EXPLAIN tbl_name is synonymous with DESCRIBE tbl_name or SHOW COLUMNS FROM tbl_name.
* When you precede a SELECT statement with the keyword EXPLAIN, MySQL displays information from the optimizer about the query execution plan. That is, MySQL explains how it would process the SELECT, including information about how tables are joined and in which order.


EXPLAIN
Quote:

http://dev.mysql.com/doc/refman/5.0/en/explain.html


or PROFILE
Quote:

http://dev.mysql.com/doc/refman/5.0/en/show-profiles.html