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