You should only need to do your second query and use the SQL_CALC_FOUND_ROWS and a new query using SELECT FOUND_ROWS()to minimize the number of times you need to _actually_ search your database.
see: http://dev.mysql.com/doc/mysql/en/SELECT.html and: http://dev.mysql.com/doc/mysql/en/Information_functions.html for more details on using these function to save trips through your data. I would also look at the EXPLAIN of your query to see if there are any hint available there to optimize your query. Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Aasef Iqbal <[EMAIL PROTECTED] To: [EMAIL PROTECTED] m> cc: Fax to: 06/28/2004 07:15 Subject: slow query when searching database of over 2 million records AM Please respond to aneedz Hi, I am working on a web project project where one of my pages has to show a count of total number of matches found and short text for few of them, just like a search engine. I need to issue two queries first one fetches a count for total matches found and second query finds detail to 10/20 of those results as specified by the user. the quries are select count(distinct CURL) as rc from tbl_directory where MATCH (CName,DSCR) AGAINST ("software project management" ) and LinkType = 1 (Time Taken for Total Count :: 207.27026605606) select distinct CURL,CName,DSCR, MATCH (CName,DSCR) AGAINST ("software project management" ) as rel from tbl_directory where MATCH (CName,DSCR) AGAINST ("software project management" ) and LinkType = 1 limit 0, 10 (0.26 seconds) first query always takes much longer time whenever I search for something new, n in subsequent searches the result is quite satisfactory (as it comes from query cache) is there anyway to speed up the search, im querying some 2 million records. thanx in adavnce Asif Iqbal Cool Things Happen When Mac Users Meet! Join the community in Boston this July: www.macworldexpo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
