List:General Discussion« Previous MessageNext Message »
From:Harini Raghavan Date:October 5 2005 3:21pm
Subject:No of records
View as plain text  
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

select count(emp.id)  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 '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

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

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