List:General Discussion« Previous MessageNext Message »
From:Harini Raghavan Date:October 5 2005 6:21pm
Subject:Re: No of records
View as plain text  
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
>
>
>  
>
Thread
No of recordsHarini Raghavan5 Oct
  • Re: No of recordsDan Nelson5 Oct
  • Re: No of recordsSGreen5 Oct
    • Re: No of recordsHarini Raghavan5 Oct