Select n rows without LIMIT mysql

Question

I have a problem. I would like to get only 300 rows from table without touching LIMIT. I need LIMIT for pagination. Is this possible in MySQL?

My current query:

SELECT a.title, a.askprice, a.picture, a.description, a.userid, a.id 
    FROM mm_ads AS a WHERE a.category = 227 AND a.status = 1 
    ORDER BY id DESC LIMIT 40,20

Edit:

Simple explanation: I need to get from a system last 300 ads but I need to keep pagination, because I don't want to have 300 rows listed in one page..

Answer 1

SELECT *
FROM (
   SELECT a.title, a.askprice, a.picture, a.description, a.userid, a.id 
   FROM mm_ads AS a 
   WHERE a.category = 227 AND a.status = 1 
   ORDER BY id DESC 
   LIMIT 300
) t
LIMIT 40,20

If the purpose is to speed up the query, then you can create a composite index:

ALTER TABLE `mm_ads` 
    ADD INDEX `mm_ads_index` (`category` ASC, `status` ASC, `id` DESC);

Answer 2

Use SQL_CALC_FOUND_ROWS after your SELECT:

SELECT SQL_CALC_FOUND_ROWS *

EDIT:

And in php, run this row to get the amount of rows:

list($int_rows) = mysql_fetch_row(mysql_query("SELECT FOUND_ROWS()"));

This will go through all the rows, get the total amount, but not fetch all the rows.

EDIT2:

May have misunderstod your question, however this is a common solution for pagination.