10
How to get total rows in mysql if there was no LIMIT clause? (lemmy.pe1uca.dev)
submitted 2 months ago by pe1uca@lemmy.pe1uca.dev to c/programming@programming.dev
4 comments fedilink hide all child comments

I was using SQL_CALC_FOUND_ROWS and SELECT FOUND_ROWS();
But this has been deprecated https://dev.mysql.com/doc/refman/8.0/en/information-functions.html#function_found-rows

The recommended way now is first to query with limit and then again without it selecting count(*).
My query is a bit complex and joins a couple of tables with a large number of records, which makes each select take up to 4 seconds, so my process now takes double the time compared to as I just keep using found rows.

How can I go back to just running the select a single time and still getting the total number of rows found without the limit?

all 5 comments
sorted by: hot top controversial new old
[-] solrize@lemmy.world 6 points 2 months ago

Select count(*) from (select ...) ? See if the query optimizer pipelines that.

Maybe I misunderstand what you are trying to do.

[-] marcos@lemmy.world 3 points 2 months ago

The OP wants the paginated table data and the count. With a single query execution.

[-] xmunk@sh.itjust.works 2 points 2 months ago

In normal SQL you'd use a window function to do this but I don't know if mysql supports that.

[-] TehPers@beehaw.org 2 points 2 months ago* (last edited 2 months ago)

Would it work to write the query as a common table expression, then select your columns from that table and join it with a count(*) aggregation of the table?

this post was submitted on 16 Aug 2024
10 points (100.0% liked)

Programming

17303 readers
51 users here now

Welcome to the main community in programming.dev! Feel free to post anything relating to programming here!

Cross posting is strongly encouraged in the instance. If you feel your post or another person's post makes sense in another community cross post into it.

Hope you enjoy the instance!

Rules

Rules

  • Follow the programming.dev instance rules
  • Keep content related to programming in some way
  • If you're posting long videos try to add in some form of tldr for those who don't want to watch videos

Wormhole

Follow the wormhole through a path of communities !webdev@programming.dev

founded 1 year ago
MODERATORS
snowe@programming.dev
Ategon@programming.dev
MaungaHikoi@lemmy.nz