List:General Discussion« Previous MessageNext Message »
From:Dan Nelson Date:October 5 2005 3:31pm
Subject:Re: No of records
View as plain text  
In the last episode (Oct 05), Harini Raghavan said:
> 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?

Since your WHERE clause has LIKE "%..." comparisons, you're doing a
full table scan on emp for both queries.  How about in the first query,
selecting emp.id instead of count(emp.id), and for the 2nd query, put
"AND emp.id IN (insert first 20 ids here)" in place of any comparison
on the emp table?  That should make the 2nd query return almost
immediately.

A better solution would be to normalize emp.title out into its own
table so you would just have ".. AND (emp.titleid = title.id AND
title.desc='Vice President') ", which would most likely let mysql use
index lookups instead of a full table scan.
 
> 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

-- 
	Dan Nelson
	dnelson@stripped
Thread
No of recordsHarini Raghavan5 Oct
  • Re: No of recordsDan Nelson5 Oct
  • Re: No of recordsSGreen5 Oct
    • Re: No of recordsHarini Raghavan5 Oct