| Re: Record pagination with count of total rows [message #170475 is a reply to message #170423] |
Tue, 02 November 2010 10:32   |
alvaro.NOSPAMTHANX
Messages: 277
Registered: September 2010
Karma:
|
Senior Member |
|
|
El 01/11/2010 13:42, matt escribió/wrote:
> I'm struggling with a concept here, and I'd like to pick some brains.
> I've got a series of report pages with complicated queries that return
> row counts in the 10s of thousands. These are paginated, and after
> accepting search and sort criteria from the user, I'm displaying a
> header that looks like this:
>
> Search contains 29,657 records. Records per page [select 25, 50, 100]
> Page [1] 2 3 ... 1186 1187 Next>>
>
> To get record totals and then deduce page numbers, I'm pulling a full
> recordset from MySQL and using PHP to display the appropriate rows.
> The problem is that, in the example above, at least, that query takes
> about 70 seconds to run. If I were to limit the search criteria so
> that it only returns<100 rows, then it's lightning fast. We're using
> ndb-cluster, so JOINs really have a performance impact.
>
> This leads me to believe I could take advantage of LIMIT, however, I
> don't see how I'd get my total row count anymore.
In MySQL, you can use the SQL_CALC_FOUND_ROWS option:
SELECT SQL_CALC_FOUND_ROWS foo
FROM bar
LIMIT 200,10;
SELECT FOUND_ROWS() AS total_rows;
For further reference:
http://dev.mysql.com/doc/refman/5.1/en/information-functions.html#function_ found-rows
In other DBMS, good old COUNT(*) tends to work well enough.
>
> Usually these reports are one-to-one with a particular table, so I've
> entertained the idea of running a separate COUNT query against that
> table with no JOINs. This won't suit my purposes because in a lot of
> cases, the search criteria are against fields in JOINed tables.
>
> I've also thought about using temporary tables that are periodically
> updated (think Oracle's static views.) The problem there is that a
> lot of times, our users will go directly from a data entry page to a
> report and expect it to reflect the new data that was just entered.
> If I use these "snapshot" tables, I would also need to implement a
> mechanism that refreshed them whenever data is inserted/updated/
> deleted elsewhere.
>
> Finally, the last approach I've considered is what I call the
> "PeopleSoft" method. That is, if you want a report, you enter all of
> your criteria, choose a format (XLS, HTML, etc.) and the report goes
> into a queue. You don't wait for your results, but instead go to a
> process management page, which would likely be AJAX enabled to inform
> you when the report was built and ready for viewing. I think this
> would be the least convenient solution for our users.
>
> I'm just curious to see how other people have addressed this problem
> and what you've found to be the up/down sides of your chosen solution.
>
> And, yes, the DBA is actively investigating why ndb is running much
> slower than the same queries on InnoDB or MyISAM. That's a separate
> post. For the purposes of this discussion, I'm interested in software
> solutions.
You can also try MySQL's result cache:
http://dev.mysql.com/doc/refman/5.1/en/query-cache.html
I don't know if it'll work in your set-up but you can have a look at it.
--
-- http://alvaro.es - Álvaro G. Vicario - Burgos, Spain
-- Mi sitio sobre programación web: http://borrame.com
-- Mi web de humor satinado: http://www.demogracia.com
--
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]