| Re: Record pagination with count of total rows [message #170454 is a reply to message #170452] |
Mon, 01 November 2010 20:33   |
![matt[1] is currently offline](/forum/theme/default/images/offline.png "matt[1] is currently offline")
matt[1]
Messages: 40
Registered: September 2010
Karma:
|
Member |
|
|
On Nov 1, 2:47 pm, 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).
We're actually using procedures. Views that use aggregate functions
won't take advantage of the query optimizer and instead will only be
processed after MySQL has built the full result set. And it scales
linearly, but NOW, this is off topic.
> 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.
We use a custom Framework, not Zend. I'm looking for general
implementation methods opposed to solutions implemented by specific
technologies.
> 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.
I suggested this in my post. The problem is that they would need to
be refreshed very often. So often in fact that there may not be any
benefit to this.
> 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.
Had I done that, I wouldn't have gotten an excellent idea from Erwin
regarding using $_SESSION to hold primary keys. This site is
implemented using PHP. I specifically addressed the fact that DB
solutions are being investigated on another path and that I was
looking specifically for software-based solutions. I went out of my
way, in fact, to demonstrate that this is on topic.
Since you also seem to have missed the part where I explained what
methodologies I had considered, and only quote the first few
paragraphs of my question, I get the feeling you only read so far
before you replied and snipped everything else that might have given
you a little more context.
I appreciate your willingness to reply, but go ahead and spare me the
condescension next time.
|
|
|
|
Calendar
Search
Help
Members
Register
Login
Home
]