Programming

17366 readers

398 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

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 (1 children)

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?