Select n rows without LIMIT mysql

Question

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

My current request:

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:

A simple explanation: I need to get the last 300 ads from the system, but I need to keep the pagination, because I do not want to have 300 lines listed on 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 goal is to speed up the query, 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 line to get the number of lines:

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

This will go through all the lines, get the total amount, but not extract all the lines.

EDIT2:

You may have misunderstood your question, but this is a general pagination solution.

Answer 3

A simple solution is Suitable for calculating only the required amount of result and using it in your pagination, then use the limit in your request to load data on each page

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

It doesn’t matter how big your database is, it gives only 20 results (although it is looking for a complete database)

Answer 4

Another thing you can do is simply extract all 300 rows from the database and store them in an array and then split the indexes of the array