| Re: Record pagination with count of total rows [message #170452 is a reply to message #170423] |
Mon, 01 November 2010 18:47   |
Thomas 'PointedEars'
Messages: 701
Registered: October 2010
Karma:
|
Senior Member |
|
|
matt 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.
Temporary tables (or views) do not scale well. So I recommend to make *two*
queries; one with LIMIT and all the fields that you need for displaying one
page of the table, the other without LIMIT (but with a computed COUNT(…)
field if you GROUP BY values for a WHERE filter) and only one primary key
field (since it is only one field per record to retrieve, retrieval of the
row count should be a lot faster).
My approach was to pass a special, optional parameter to the retrieval
method of the model mapper class, so that it built a slightly different
query (very easy to do with Zend Framework), and returned either the number
of rows in the result (without filter) or the value of the computed field
(with filter) then.
Depending on your application and the complexity of the query (the more you
JOIN, the slower it is going to be), it might be a good idea to
create/update flat tables when data is saved, and run the query for
displaying the data only on the flat table. Magento E-Commerce Shop (?AMP)
does that; it appears to work fine, and queries are quite fast considering
the complexity of the Magento database.
Evidently your question has nothing to do with PHP programming as such, and
is therefore off-topic here. Please read
<http://www.catb.org/esr/faqs/smart-questions.html> and adhere to it in the
future.
PointedEars
--
var bugRiddenCrashPronePieceOfJunk = (
navigator.userAgent.indexOf('MSIE 5') != -1
&& navigator.userAgent.indexOf('Mac') != -1
) // Plone, register_function.js:16
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]