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