SQL_CALC_FOUND_ROWS Deprecated

[Dani]

I know that SQL_CALC_FOUND_ROWS has been deprecated, but I was just wondering if anyone knows why? It was so handy, even if slightly less performant than running the query twice (once with and once without the LIMIT clause).

[Salem]

There's a detailed writeup of why here.

https://dev.mysql.com/worklog/task/?id=12615

[Dani]

Guess I'm going to have to go through all my code now :(

I must also have logs set to only log errors because I'm not seeing the deprecation notices in my log file.

[Dani]

Soooo that totally didn't take as long as I was afraid it would. :)

Luckily I had been already slowly migrating away from SQL_CALC_FOUND_ROWS so there were only about a dozen left to refactor.

[Dani]

Okay, so I've run into a big of a snafu and I'm trying to figure out which is more efficient:

SELECT COUNT(DISTINCT id) AS total_rows
FROM table
LEFT JOIN [...]

or

SELECT COUNT(*) AS total_rows FROM
(
    SELECT 1
    FROM table
    LEFT JOIN [...]
    GROUP BY id
)

In other words, as I now have to do a second query to fetch the total number of records, which is faster: using a COUNT DISTINCT, or a COUNT(*) with GROUP BY and subquery?

Of course, I'm oversimplifying the queries, but that's the trade-off.

[Dani]

Bump?

[pritaeas]

I have a feeling that the query optimizer would recognize both as being the same.

[Dani]

Thank you!