Harini Raghavan <[EMAIL PROTECTED]> 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
