List:General Discussion« Previous MessageNext Message »
From:SGreen Date:October 5 2005 3:39pm
Subject:Re: No of records
View as plain text  
Harini Raghavan <harini.raghavan@stripped> wrote on 10/05/2005 
11:21:26 AM:

> Hi,
> I have implemented pagination for the search functionality in my 
> application. Since I have a lot of data, I thought in memory paging 
> would not be a good option and opted for paging at the database layer.
> To do this I am retrieving the no of records using the following query 
> and then again executing another query which actually limits the no of 
> records to 20. I am facing performance issues with the query. Since I am 

> executing this complex query twice, once to get the records and another 
> time to get the first 20 records, the execution time is double and is 
> very slow.
> I can probably bring down the execution time by half if I have any other 

> way to get the no. of records. Does anyone have any suggestions?
> -Harini
> 
<snipped first query>
> 
> select emp.id as id, exec1.firstName as firstName, exec1.lastName as 
> lastName,  exec1.id as execId, comp.name as name, comp.ticker as ticker, 

> emp.title as title  from executive as exec1 , company comp, 
> target_company targetComp, employment as emp where emp.executive_id = 
> exec1.id  and emp.company_id = comp.id  and comp.id = 
> targetComp.company_id  and ((emp.title like 'V.P.' OR emp.title like 
> 'V.P. %' OR emp.title like 'V.P., %' OR emp.title like 'V.P.. %' OR 
> emp.title like '% V.P. %' OR emp.title like '% V.P., %' OR emp.title 
> like '% V.P.. %' OR emp.title like '% V.P.' OR emp.title like '% V.P..') 

> OR (emp.title like 'Vice-President' OR emp.title like 'Vice-President %' 

> OR emp.title like 'Vice-President, %' OR emp.title like 'Vice-President. 

> %' OR emp.title like '% Vice-President %' OR emp.title like '% 
> Vice-President, %' OR emp.title like '% Vice-President. %' OR emp.title 
> like '% Vice-President' OR emp.title like '% Vice-President.') and 
> emp.active = 1  order by lastName, firstName, id  limit 0,20
> 


Only execute the second query after you add the clause SQL_CALC_FOUND_ROWS 
to it. After the query comes back, you not only have the first 20 rows, 
you can also get the number of rows you would have returned if you didn't 
have the LIMIT clause by calling the function SELECT FOUND_ROWS().

http://dev.mysql.com/doc/mysql/en/select.html
http://dev.mysql.com/doc/mysql/en/information-functions.html

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


Thread
No of recordsHarini Raghavan5 Oct
  • Re: No of recordsDan Nelson5 Oct
  • Re: No of recordsSGreen5 Oct
    • Re: No of recordsHarini Raghavan5 Oct