Hi Green,
I tried using SQL_CALC_FOUND_ROWS as suggested by you. I am executing
some other queries following the query that has the clause
SQL_CALC_FOUND_ROWS. The documentation says in such a case we can save
the row count using *|SET @rows = FOUND_ROWS(); |*How can I retrieve
this row count value later?
-Harini
SGreen@stripped wrote:
>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
>
>
>
>
