I want to make a request to the MySQL database, I need to get only 5 records from it, but at the same time I need to count the total number of records that satisfy my condition.

That is, I need the line of the user request to bring him to 1 page only 5 records, but the number of pages will have to be calculated from the total number of records found.

I understand that you can make a request without a limit, but then too much data will return to me, and if you make 2 requests: 1 receives the number of records, and the second receives 5 records, you will have to join the database 2 times and spend extra time searching again.

The question is whether it is possible to do this somehow in 1 SQL query or to minimize the cost of resources with 2 queries. Correct if something is not understood.

Thanks in advance for any help.

5 answers 5

Original answer

This can be done using window functions , but they have been entered into MySql only from version 8 (see article or here ). Here is an example of use:

create table test ( id integer, field integer ); insert into test (id, field) values (1,10); insert into test (id, field) values (2,10); insert into test (id, field) values (3,10); insert into test (id, field) values (5,20); SELECT id , count(*) OVER() AS full_count FROM test WHERE field=10 LIMIT 2;

And a visual result of the work (but for postgress, because the specified resource only allows you to test the query for MySql 5.6)

Test result on Postgresql

PS The only moment that you have in each record will be the total number of records from the sample. But I do not think that this will be a significant problem)

  • Thank you, this is exactly what I was looking for - Eltsov Danil
  • @EltsovDanil, in this case, mark the answer as correct. - Temka, too,

Dig in the direction of SQL_CALC_FOUND_ROWS.

     with cte as (select * from t where ...) select *, (select count(*) from cte) qty from cte limit 5 ;
    • The author in the text indicates that it is about MySQL, but does not indicate the version. So in general, it should be considered that cte are not available. - Akina
    • @Akina, no cte rewrite is not a problem. Right? - msi
    • Formally, yes. But why? when there are regular funds indicated by comrades JVic and MAX . - Akina
    • The author asked to do in one request. I replied. - msi

    You need to create a scrolled Statement , by default ResultSet can only move forward, that is, if you try to count the number of rows returned, you cannot return back. We need to do something like:

     //скроллируемый указатель только для чтения stmt = con.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); ResultSet rs = stmt.executeQuery("SELECT * FROM " + dbName); rs.last(); //двигаем указатель на последнюю запись int recs=rs.getRow(); //получаем количество записей rs.beforeFirst(); //двигаем курсор перед первой записью while (rs.next()) { //работаем с записями }

      There have already been some good answers, but let me say something too :) and this:

      1. you can look here -> here <- it seems like there is an answer to your question

      2. you can just get all the records and at the language level get the necessary 5 records and the total number in the database. like so

        resultList.size() //дает общее количество for (int i = 0; i < 5; i++) { System.out.println(mc.toString()); }

      3. can use with the procedure, only in this case you will have to write two rezaltset

        CREATE DEFINER = 'test'@'%' PROCEDURE test.procedure1 () BEGIN SELECT COUNT(t.id) AS count FROM table m; SELECT t.id, t.state, t.name FROM table t LIMIT 5; END