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?

you are viewing a single comment's thread
view the rest of the comments

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

this post was submitted on 16 Aug 2024

10 points (100.0% liked)

Programming

17303 readers

41 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

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