计算数据库中的记录总数，但仅返回X记录

Question

We are shifiting one of our applications from PostGreSQL to MySQL. We have the following query in PostGres -

SELECT 
      idrte
    , left_name
    , gacodemun
    , clsrte
    , speed
    , speed_override
    , ST_AsGeoJSON(geometry) AS geometry
    , count(idrte) OVER() AS total 
FROM 
    aq_routes 
WHERE 
    ST_CONTAINS(
        GeomFromText(
            'Polygon(($geom))'
        ),geometry) 
GROUP BY 
      idrte
    , left_name
    , gacodemun
    , clsrte
    , speed
    , speed_override
    , geometry 
ORDER BY left_name 
LIMIT 150;

We are trying to port this query to MySQL ad have come up with this -

SELECT 
     id AS id
   , left_name AS left_name
   , left_locality AS left_locality
   , class AS class
   , speed AS speed
   , speed_override AS speed_override
   , AsWKB(Geom) AS geom
   , count(id) AS total 
FROM road_segments 
WHERE 
   CONTAINS(
       GeomFromText(
           'Polygon(($geom))'
       ),geom) 
GROUP BY 
      id
    , left_name
    , class
    , speed
    , speed_override
    , geom 
ORDER BY left_name 
LIMIT 150;

There are some field changes, but on the whole the intended functionality is to be the same.

You'll notice the call the OVER() in the PostGreSQL. The table we are querying has a lot of data. So to the user, we display only 150 records but tell him the count of all the records. With PostGres this was possible through a single call but we don't have OVER() function in MySQL.

What other alternatives do we have other than querying the database a second time. A second query to the database is increasing the response time by at least 15 seconds and sometimes even timing out.

Answer 1

I think you want to use FOUND_ROWS() and CALC_FOUND_ROWS(). You can review the documentation here.

The basic idea is:

select CALC_FOUND_ROWS id, . . .
. . .

Then use the function FOUND_ROWS() to return the value to the application.

Unfortunately, to get this value as a column value, I think you would need to run the query twice, essentially:

select . . ., x.cnt
from . . . cross join
     (select count(*) as cnt from <your query here>) x
. . .