2

I have a large database in which I use LIMIT in order not to fetch all the results of the query every time (It is not necessary). But I have an issue: I need to count the number of results. The dumbest solution is the following and it works:

We just get the data that we need:

SELECT * FROM table_name WHERE param > 3 LIMIT 10

And then we find the length:

SELECT COUNT(1) FROM table_name WHERE param > 3 LIMIT 10

But this solution bugs me because unlike the query in question, the one that I work with is complex and you have to basically run it twice to achieve the result.

Another dumb solution for me was to do:

SELECT COUNT(1), param, anotherparam, additionalparam FROM table_name WHERE param > 3 LIMIT 10

But this results in only one row. At this point I will be ok if it would just fill the count row with the same number, I just need this information without wasting computation time.

Is there a better way to achieve this?

P.S. By the way, I am not looking to get 10 as the result of COUNT, I need the length without LIMIT.

user9102437
  • 600
  • 1
  • 10
  • 24
  • Select count(*) is usually faster than select * or a list of columns. I don't think there's any result column you can add to your select to give you the number that you want. Even if there were, I think there's a good chance it would be slower than doing the two queries separately because it would have to gather all the data from all the qualifying rows before truncating to LIMIT rows. – Simon Goater Dec 25 '22 at 13:47

2 Answers2

1

You should (probably) run the query twice.

MySQL does have a FOUND_ROWS() function that reports the number of rows matched before the limit. But using this function is often worse for performance than running the query twice!

https://www.percona.com/blog/2007/08/28/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/

...when we have appropriate indexes for WHERE/ORDER clause in our query, it is much faster to use two separate queries instead of one with SQL_CALC_FOUND_ROWS.

There are exceptions to every rule, of course. If you don't have an appropriate index to optimize the query, it could be more costly to run the query twice. The only way to be sure is to repeat the tests shown in that blog, using your data and your query on your server.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • Just another reason for not using FOUND_ROWS(): [Warning 1287 FOUND_ROWS() is deprecated](https://dev.mysql.com/worklog/task/?id=12615#:~:text=Warning%201287%20FOUND_ROWS()%20is%20deprecated%20and%20will%20be%20removed%20in%20a%20future%20release.%20Consider%20using%20COUNT(*)%20instead.). – Luuk Dec 25 '22 at 20:02
  • Thanks, never would have thought that such a reasonable feature will be absent. Well, not completely, but be such a problem. – user9102437 Dec 25 '22 at 20:13
0

This question is very similar to: How can I count the numbers of rows that a MySQL query returned?

See also: https://mariadb.com/kb/en/found_rows/

This is probably the most efficient solution to your problem, but it's best to test it using EXPLAIN with a reasonably sized dataset.

Todd Christensen
  • 1,297
  • 8
  • 11
  • It's the P.S. ("P.S. By the way, I am not looking to get 10 as the result of COUNT, I need the length without LIMIT.") under the question that makes me ask if this is a correct answer.... – Luuk Dec 25 '22 at 14:58