Performance question in combination with a pager.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
eromein
Forum Newbie
Posts: 7
Joined: Fri May 06, 2005 7:35 am

Performance question in combination with a pager.

Post by eromein »

Hi,

I’m using a pager (page-slider), you know, a indicator that show the number of pages, your current page and a previous and next arrow.

I want to use this to boost performance, so I don’t need to read ALL records from the database but only a select few, being the records that are shown on that page.

But my pager needs to know how many records there are in total. So this means I need to do a normal query to figure out how many records I need. But then I do not have a performance advantage.

I query the database every time I load the page.



Is a query with a LIMIT faster then a query without one?
Is there a way to get the number of record in a table in a quicker way then a “normal” query?
Should I not query the database every time I reload the page?

How do you guys do stuff like this?

Hope to get some answers!

Kind regards,

Emiel Romein
Netherlands, the
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Re: Performance question in combination with a pager.

Post by timvw »

eromein wrote:Is a query with a LIMIT faster then a query without one?
Depends on the DBMS that you are using. But from the point: only ask what you need it seems a good idea to limit the number of rows in the resultset to the number of rows you will be displaying.
eromein wrote: Is there a way to get the number of record in a table in a quicker way then a “normal” query? Should I not query the database every time I reload the page?
There is nothing wrong with

Code: Select all

SELECT COUNT(*) AS count FROM table WHERE ..
I usually only perform that query only when people change the WHERE attribute in my QueryBuilder class :)
eromein
Forum Newbie
Posts: 7
Joined: Fri May 06, 2005 7:35 am

Post by eromein »

Let's get more specific. I'm using mySQL and I've programmed the query below.

Is there a way the calculate the number of records in the result that is quicker then the query like it is now?

Code: Select all

$query = 
          "SELECT table_id, company_name, record_id 
           FROM tabledata 
           WHERE 
             (".$this->qcomp['table'].") AND
             (".$this->qcomp['company'].") AND
             record_id 
               IN 
                 (SELECT 
                    record_id 
                  FROM 
                    tabledata 
                  WHERE 
                    ($filter)) 
           GROUP BY 
             table_id, company_name, record_id";
Kind regards,

Emiel Romein
Netherlands, the
Post Reply