MYSQL ->> Use of LIMIT keyword

• Sometimes Normally it would prefer to do a full table scan.

• If you use LIMIT row_count with ORDER BY, MySQL ends the sorting as soon as it has found the first row_count rows of the sorted result, rather than sorting the entire result. If ordering is done by using an index, this is very fast.
  

• When combining LIMIT row_count with DISTINCT, MySQL stops as soon as it finds row_count unique rows.

• In some cases, a GROUP BY can 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, LIMIT row_count does not calculate any unnecessary GROUP BY values.

• 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 0 quickly 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 displays Empty set in such cases; you should instead use SHOW COLUMNS or DESCRIBE for this purpose.)

• When the server uses temporary tables to resolve the query, it uses the LIMIT row_count clause to calculate how much space is required.

Example :-

select * from dbName.emp limit 10;

The above query retrive only 10 record from emp table.But some times we need interval type of record such as:- 'emp' table has 100 record but , Every time we need 10 record at that time the query like below :

select * from dbName.emp limit 0,10;
select * from dbName.emp limit 10,10;
...
..
..
..
..
Like this.

Case Studies:-MySQL LIMIT is Not working
I got question that LIMIT is not working properly,but actually as per the manual kit of mysql LIMIT work like below :

The first parameter indicates the starting record (first record is 0). The second parameter is the number of records to return, NOT the last record to return.

LIMIT 0, 10
This will return 10 records starting at record 0 (i.e. 0 - 9)

LIMIT 10, 20
This will return 20 records starting at record 10 (i.e. 10 - 29)

LIMIT 10, 10
This will return the 10 records starting at record 10 ( 10 - 19).


Example :--
select * from dbName.emp limit 10,10;




Posted By:-javadevelopersguide
Follow Link-http://javadevelopersguide.blogspot.com