- 7.2 Optimizing SELECT and Other Statements
- 7.2.1 Optimizing Queries with EXPLAIN
- 7.2.2 Estimating Query Performance
- 7.2.3 Speed of SELECT Queries
- 7.2.4 WHERE Clause Optimization
- 7.2.5 Range Optimization
- 7.2.6 Index Merge Optimization
- 7.2.7 Condition Pushdown Optimization
- 7.2.8 IS NULL Optimization
- 7.2.9 LEFT JOIN and RIGHT JOIN Optimization
- 7.2.10 Nested-Loop Join Algorithms
- 7.2.11 Nested Join Optimization
- 7.2.12 Outer Join Simplification
- 7.2.13 ORDER BY Optimization
- 7.2.14 GROUP BY Optimization
- 7.2.15 DISTINCT Optimization
- 7.2.16 Optimizing IN/=ANY Subqueries
- 7.2.17 LIMIT Optimization
- 7.2.18 Using optimizer_switch to Control the Optimizer
- 7.2.19 How to Avoid Table Scans
- 7.2.20 INFORMATION_SCHEMA Optimization
- 7.2.21 Speed of INSERT Statements
- 7.2.22 Speed of UPDATE Statements
- 7.2.23 Speed of DELETE Statements
- 7.2.24 Other Optimization Tips
In some cases, MySQL handles a query differently when you are
using LIMIT
and not using
row_countHAVING:
If you are selecting only a few rows with
LIMIT, MySQL uses indexes in some cases when normally it would prefer to do a full table scan.If you use
LIMITwithrow_countORDER BY, MySQL ends the sorting as soon as it has found the firstrow_countrows of the sorted result, rather than sorting the entire result. If ordering is done by using an index, this is very fast. If a filesort must be done, all rows that match the query without theLIMITclause must be selected, and most or all of them must be sorted, before it can be ascertained that the firstrow_countrows have been found. In either case, after the initial rows have been found, there is no need to sort any remainder of the result set, and MySQL does not do so.When combining
LIMITwithrow_countDISTINCT, MySQL stops as soon as it findsrow_countunique rows.In some cases, a
GROUP BYcan be resolved by reading the key in order (or doing a sort on the key) and then calculating summaries until the key value changes. In this case,LIMITdoes not calculate any unnecessaryrow_countGROUP BYvalues.As soon as MySQL has sent the required number of rows to the client, it aborts the query unless you are using
SQL_CALC_FOUND_ROWS.LIMIT 0quickly returns an empty set. This can be useful for checking the validity of a query. When using one of the MySQL APIs, it can also be employed for obtaining the types of the result columns. (This trick does not work in the MySQL Monitor (the mysql program), which merely displaysEmpty setin such cases; you should instead useSHOW COLUMNSorDESCRIBEfor this purpose.)When the server uses temporary tables to resolve the query, it uses the
LIMITclause to calculate how much space is required.row_count
User Comments
When one is using a LIMIT attribute in their query ,
it's often fallowed by an OFFSET. These two togeather
are often used in pagination ( paging of results ) as in.
select * from thetable limit 10 offset 0
To find out how many rows would be available if one had not
used the LIMIT / OFFSET, one would alter the statement
above to look like this
select SQL_CALC_FOUND_ROWS * from thetable limit 10 offset 0
The return from both commands looks the same, so to get the
row count you need to issue a fallowup query
select FOUND_ROWS()
So one wants to search for a particular record/row in
the database table and calculate an initial offset
value to feed into the typical pagination routines.
While there may be better ways to do this , what I cobbled
together was this little bit of MySql syntax that seems to work just fine on my little 40,000 row table.
set @row=-1;select foo.Row,foo.company_name from (select @row:= @row+1 AS Row ,company_name from TheCompanyDatabaseTable ) AS foo where foo.company_name='whatever';
Given the fact I searched in vein for this answer , I figured I'd post the answer I hacked up so it will
be here the next time I need it.
Add your own comment.