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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
