CODEWITHSUNDEEP
mysqlruby-on-rails
Rober
Rober

Reputation: 6108

How to execute query in and get count of total elements without running twice with Rails 4 and MySQL

I´m running a cost-time query in MySQL and Rails. This query is created dynamically and it also manages pagination with LIMIT and OFFSET. This is a summarized example:

SELECT fields
FROM tables
WHERE conditions
ORDER BY order DESC LIMIT ? OFFSET ?

I would also like to get the total count of elements, but I would like to avoid run the query twice for performance purposes. I don´t think is possible, but maybe you surprise me :)

Currently, I have something like:

objects = Object.find_by_sql(query)    
totalCount = objects.count

But, of course, this is always returning the limit count.

Upvotes: 1

Views: 1341

Answers (4)

Edison Vee
Edison Vee

Reputation: 1

This is a bit late, but try using objects.length. Length will count what you already have in the array.

Upvotes: 0

Daniel Lutz
Daniel Lutz

Reputation: 160

MySQL? Why not to use SQL_CALC_FOUND_ROWS with FOUND_ROWS() ?

With two queries: (the second query will not hit the database)

SELECT SQL_CALC_FOUND_ROWS * FROM users LIMIT 0,5;
SELECT COUNT(FOUND_ROWS()) AS rows_count FROM users;

But one advise: you must test it. This might be slower or faster than two queries, it depends on some factors, like caching, engine, indexes, etc...

https://dev.mysql.com/doc/refman/5.7/en/information-functions.html#function_found-rows

Is this possible to get total number of rows count with offset limit

Upvotes: 1

Abhishek Ginani
Abhishek Ginani

Reputation: 4751

To Count the records just add one query as a column to your dynamically created query.

Check this:

SELECT fields,(SELECT count(*) FROM tables WHERE conditions) as obj_count
FROM tables
WHERE conditions
ORDER BY order DESC LIMIT ? OFFSET ?

Using MySQL session variables(starting with symbol @) we can write more efficient query.

SELECT fields,@count as obj_count 
FROM tables,(SELECT @count:=count(*) FROM tables WHERE conditions) as sub
WHERE conditions
ORDER BY order DESC LIMIT ? OFFSET ?

Upvotes: 0

Rich Seviora
Rich Seviora

Reputation: 1809

Because you're using pagination and offsetting, you're not going to get a complete result. You can either run the two separate queries, or you can pull the complete dataset and then filter for pagination. The first option is likely to be faster, especially as your dataset grows.

To improve performance you'd getter better results looking at a caching strategy at various points. Without knowing when the data changes I can't offer any specific advice.

Edit 1: Expanding for Clarification

It might help to explain why this is the case. When you put into place the limit and offset manually, Rails knows nothing about the data not returned by the query. So without having that data available, it's definitionally impossible to make Rails aware of the total count.

That said, a simple COUNT aggregation should always be very fast to execute. If you're seeing speed issues with the execution of that query you'll want to make sure you have the right indexes in place, and that Rails is rendering the conditions in an optimal format.

Upvotes: 1

Related Questions