Programming

17000 readers

253 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 [email protected]

founded 1 year ago

MODERATORS

snowe

Ategon

[email protected]

How to get total rows in mysql if there was no LIMIT clause? (lemmy.pe1uca.dev)

submitted 1 month ago by [email protected] to c/programming

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

[–] [email protected] 6 points 1 month ago (1 children)

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

Maybe I misunderstand what you are trying to do.

[–] [email protected] 3 points 1 month ago

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

[–] [email protected] 2 points 1 month ago

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

[–] [email protected] 2 points 1 month ago* (last edited 1 month 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?