CODEWITHSUNDEEP
phpmysqlsqldatabasepostgresql
Abijeet Patro
Abijeet Patro

Reputation: 2884

Count the total number of records in the database but return only X records

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.

Upvotes: 0

Views: 114

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270021

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
. . .

Upvotes: 2

Related Questions