| Re: Record pagination with count of total rows [message #170453 is a reply to message #170452] |
Mon, 01 November 2010 20:05   |
Hamish Campbell
Messages: 15
Registered: September 2010
Karma:
|
Junior Member |
|
|
On Nov 2, 7:47 am, Thomas 'PointedEars' Lahn <PointedE...@web.de>
wrote:
> 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.
+1 to P.E.s suggestions.
For a complex model, de-normalization (flat tables) is probably the
best option after query plan improvements. Since you're using MySQL
you'll need to add the triggers to your ORM layer, but it is usually
straight-forward to maintain a flat version that is kept in sync (or,
nearly in sync) with the source data. PostgreSQL is particularly
excellent for setting this sort of behaviour up at a database level -
but then, PostgreSQL has views and very efficient query optimization
too :D
That said, 30,000 records is not a huge result set. I note that you've
got your DBA looking into the database performance, so I wouldn't make
any code changes until the DBA has done what (s)he can.
Hamish
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]